Permission Problem Running Sproc Using Asp.net 2.0 On Sql Server 2005
Apr 20, 2006
I have a stored procedure in my asp.net 2.0 app that will not run. I am using an sqlcommand object and executenonquery. At first I received an error "execute permission denied". I had already given the user on sql server read and write permissions. So -- since I was not passing the sql server userloginid or password in the connection string, I tried changing the connection string in my web.config to:
"data source=xxxSQL2005;initial catalog=AdventureWorks; User Id=myid;password=mypasswd"
now I get an error that the login has failed for user xxx. So I am wondering what I need to do to run stored procedure in a web app. I am using the full sql server version not express.
i have a sproc that is taking 5 hours+ to run. that was on 40000 rows. even on 25 rows it takes over an hour. the sproc calls a sproc or two with a cursor or two ( sorry for being so vague) . the sproc stil should not take an hour for 25 rows. i have checked what db objects are locked, and there are no other processes going on at the time, as i have killed all them off. would lack of space on a hard disk or something. any ideas of some strange things that could cause a sproc to run slowly?
My development environment is IIS 5.1, asp.net 2.0, Visual Web Developer 05 Express, MS Sql 2005 Express with XP Pro. I used a "stored procedure" in a webpage Formview to insert a record in a child table after inserting a record in the parent table. All went well when testing in VWD. After deploying to remote site on same machine, I get an error "EXECUTE permission denied on object 'usp_Insertdataset', database 'Job_Tracker_SQL', schema 'dbo'"
when trying to insert. I know that SQL Express is not suppose to support stored procedures. Is there a work around? I need to host this site on this machine for the immediate future.
We have a DTS which loads files using FTP to another server. This will be used by app groups and will be run via click of button using sp_start_job,
Somehow the userid running the job gets "not sysadmin to run cmdshell.." Top fix that, we granted it to execute the xp_cmdshell and also created a proxy account for SQL Agent which is an admin on server and sql. However, still we get permission errors like "...The needed permission is missing to run command shell.." Please help . The DTS is being called from SQl Agent job and we don't want an admin ID to be used .
I have a SQL-authenticated user that I want to be able to run a pass-thru query. The user was created solely for this particular job. I've given it appriopriate permissions on all the tables, views, and stored procedures directly involved in the task. BUT, the task fails. If I have the query perform a simple task, like SELECT * FROM TABLE, it works, but, not on this:
CREATE PROCEDURE sp_gtn_RunFromAccess AS EXEC msdb..sp_start_job @job_name='CA_GTN Download From Mainframe' GO
If I give the user the DB_OWNER role, it fails; however, if I make it a sys admin, it suceeds. That is obviously no good. It also suceeds it I put sa and a password in the ODBC string, but that is unacceptable, as well.
What kinds of permission am I overlooking? Any thoughts on this? Thanks, Michael G
We resent installed SQL Server 2005 and I am very new to It. Here is what I have done.
Created a new user in SQL server and denied everything for a particular table to that user so that a select * from that table will result in a permissions error. Also that user cannot view the table in SQL manager
The problem now is that that user can create a stored procedure that has the SQL statement Select * from that table and execute the stored procedure to view the table. The reason I created this user is to allow a team in the company to create stored procedures for Crystal. In theory he should be able to create stored procedures but not get any data from the denied tables. How can I achieve this?
Can u guys help me out here? I cannot connect to database from localhost. If i type http://localhost/mobile/default.aspx it will show SELECT permission denied on object 'KATEGORI', database 'mobile', schema 'dbo'. I try giving permission to ASPNET account and also IIS account to the database 'mobile' but nothing works. But if i give permission to IIS using integrated windows authentication it works in localhost but not when i use ip address example : 202.189.176.145:10334/mobile/default.aspx. It will prompt username, password and domain i think. How can i configure sql server to give permission for iis ?
I originally installed SQL Server Express 2005 on my computer using Windows Authentication mode, and discovered when I tried to add another user/login that I didn't have permission to do so. This is rather odd as the windows account that I installed SQL server with is the system admin for the computer.
I have successfully changed the login mode to mixed, and have tried to login in as "sa", but it appears that "sa" was given some sort of password (did SQL server automatically generate one?), and I don't know what it is. When I go into command prompt and try to change the password, it says that it cannot alter the login 'sa' because it does not exist or I do not have permission (i'm pretty sure it's the later, as 'sa' shows up on the list of logins in SQL server express).
I'm having trouble doing a basic permission, I need to prevent one user from seeing one column in a view, such as a students phone number. However, when I deny the user from seeing the column it denys the user from viewing the entire view. Can someone tell me the correct way to deny the user from just the one column.
We have a web app that accesses Sql Server 2000 using windows authentication. We recently installed SQL Server 2005 on a new server and are in the process of porting the data from SS2000 over to 2005. I expected some problems, but I've run into something that seems like it should be relatively simple to solve, yet I have been banging my head against the wall for a couple of days. After changing the connection string in web.config of the web app to point to the new SQL Server 2005 database, I keep getting ... SELECT permission denied on object 'tUser', database 'GDoc', schema 'dbo'. The problem is, when I go into Management Studio and check the permissions, the user I'm logged in as DOES have select permissions on that database. What am I not doing? Am I forgetting something simple?
I have an application that uses Integrated Windows authentication. My Web.config looks like below <add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" /> When users try to access my application, they get the below error: Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo' The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse. Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO. Thanks,
Setting status of unmanaged components and removing unmanaged resources
Error Code: 29506 MSI (s) (20:FC) [09:07:19:951]: Leaked MSIHANDLE (15949) of type 790531 for thread 1116 MSI (s) (20:FC) [09:07:19:951]: Leaked MSIHANDLE (15835) of type 790531 for thread 1116 MSI (s) (20:FC) [09:07:19:951]: Note: 1: 2769 2: Do_sqlFileSDDL.D20239D7_E87C_40C9_9837_E70B8D4882C2 3: 2 MSI (s) (20:28) [09:07:19:998]: I/O on thread 2876 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 3704 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 172 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 1360 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 1908 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 3180 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 3836 could not be cancelled. Error: 1168 MSI (s) (20:28) [09:07:19:998]: I/O on thread 2784 could not be cancelled. Error: 1168 MSI (c) (7C:58) [09:07:19:951]: Disallowing shutdown. Shutdown counter: 0 Error 29506. SQL Server Setup failed to modify security permissions on file C:Windowssystem32SQLAGENTCTR90.DLL for user Rick. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive. MSI (s) (20!5C) [09:07:19:998]: Product: Microsoft SQL Server 2005 -- Error 29506. SQL Server Setup failed to modify security permissions on file C:Windowssystem32SQLAGENTCTR90.DLL for user Rick. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
MSI (s) (20:74) [09:07:20:014]: Executing op: ActionStart(Name=Rollback_SetServerSSNLDefaults.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Setting Client Network Default Settings,) Info 2769.The installer has encountered an unexpected error. The error code is 2769. Custom Action Do_sqlFileSDDL.D20239D7_E87C_40C9_9837_E70B8D4882C2 did not close 2 MSIHANDLEs. MSI (s) (20:74) [09:07:20:045]: Executing op: CustomActionSchedule(Action=Rollback_SetServerSSNLDefaults.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1345,Source=BinaryData,Target=Rollback_SetServerSSNLDefaults,CustomActionData=010Setting Client Network Default Settings10000MSSQLSERVERMSSQL.1MSSQL.111)
Hi, I am running SQL Server 2005 x64 DE on Vista x64. I installed MS VS 2005 Pro. Edition. During the setup of VS, I did a custom install, un-checking the SQL Express addition box. After installation, I received errors that indicated the SQL Express did not install. Even though I do not have SQL Express installed on my machine, MS Update says that I need SQL Express SP2. My SQL DE is up to date with SP2 and MS Update indicates as "optional" that I need SQL SP2.
Is SQL Express a necessity for VS Pro? If not, why is MS Update indicating that I need SQL Express SP2? In addition, why is MS Update telling me I need SQL SP2 when it is already installed?
Note: I installed SQL SP2 manually, not through MS Update. This makes me believe that MS Update is not detecting it.
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
I open two instances Sql 2005 Server Management Studio. Instant 1 - Logon as SA Instant 2 - Logon with User Id and Password I have a sproc that executes a complex view (composit view that makes use of other views). The spoc returns different results depending if it's run from SA or my User account. I guess this is a permissions problem. The question is ....... How do I find out which view, table, function etc. is causing the problem. Is there a trace facility in Sql2005 Server Management Studio? Any help appreciated.
Hi, I am migrating my client's site from SQL 2k to 2005. The site was built on .NET 1.1 In order to run SQL 2005, do we need to upgrade to .NET 2.0 in order to run the site? If we do need to upgrade to .net 2.0, is there any way to get around the problem and run it on .NET 1.1. This is because we have a lot of 3rd party componenet which only support .NET 1.1 Thanks in advance. Paul
I've installed sql2005 on a windows 200 server. now the requirement is to install sql2000 also on the same server.
Would like to know if any have experienced and type of problem on having both sql on same server. and if there is any impact on installing sql2000 after sql2005.
I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..
please help me..If you need some more information please ask ..
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message: Msg 18483, Level 14, State 1, Line 1 Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out? (I include the reproduce steps for the first error message, followed by my resolution that generates the second error message) ====== Reproduce steps for the first error message ======
On the ComputerAInstanceA instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseA GO USE DatabaseA GO CREATE TABLE TestTable(Col1 int, Col2 varchar(50)) GO INSERT INTO TestTable VALUES (1, 'Hello World') GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseB GO USE DatabaseB GO CREATE TABLE TestTable (Col1 int, Col2 varchar(50)) GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement: USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXEC LNK_ServerB.DatabaseB.dbo.InsertA GO Then I receive the first error message.
======= My resolution that generates the second error message =======
On the ComputerBInstanceB instance, run the following statement: sp_addserver 'ComputerAInstanceA' GO sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE' GO USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA] GO Then I receive the second error message.
Hi We have a bunch of servers running server 2000 & 2003 along with many sql server (versions 2000 & 2005) databases in a production environment pulling transactions then doing warehousing & reporting.
An audit has shown up 1 production server using English_US 'mmddyyyy' system date formats, all others being English_Australian 'ddmmyyyy'.
Is it safe to simply change the regional settings on this English_US server to English_Australian or will it mess up the data in our SQL 2005 databases? I've not been able to get a definitive answer from anyone yet!
Hello, I am trying to validate a user via a stored procedure. This works OK until I try to validate the password, at which point the validation fails (even though the password is OK). Below is my sproc. If I comment out AND m.Password = @Password the sproc is OK. I am passing in the password as a string when testing the sproc. Any ideas? J. ALTER Proc [dbo].[pru_VerifyApproveUser] @UserName nvarchar(256),@Password nvarchar(128),@ApplicationName nvarchar(256)AS BEGIN IF EXISTS(Select * from dbo.aspnet_Membership WHERE Email = @UserName)BEGIN DECLARE @UserId uniqueidentifierSELECT @UserId = NULLSELECT @UserId = u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications aWHERE LoweredUserName = LOWER(@UserName) ANDu.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName ANDu.UserId = m.UserId AND m.Password = @Password -- If @Userid is Null - then the above credentials are not satisfied-- e.g. wrong password/username combo.. IF (@UserId IS NULL) BEGIN RETURN 2 END Update dbo.aspnet_Membership SET IsApproved = 1 WHERE Email = @UserName RETURN 1 END END
Can someone show me, or direct me, to a source, that shows me how, and what to change, when deploying a website from a development server running Sql Ex to a production server running Sql server 2005. I can’t get the sites to run under Sql server 2005.
They work in Sql Ex. what must I change? The connection string, to what format? and what else? I attached the dB to Sql 2005 and browsed the content in the Sql manager. But can’t get the aspx pages to work on the server.
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
Where can I further educate myself on this subject?
Right off from the start I would assume that installing SQL Server 2000 or 2005 on Windows Server 2003 that is set up as a web server hosting a website would be against "best practices." Is my assumption right?
Common sense tells me to not to host a website on a pc that is also hosting my database.
We are in the processes of finalising or server architecture for our production environment and the proposal that has been put forward is to run the database within SQL Server 2005 which is running in a virtual machine. It is thought (by some) that this provides a very high degree of redundancy as it will use ESX server and will have multiple servers in the farm that it can move to in the event of a server crash (which i admit makes a degree of sense).
I however am worried about performance. I release as soon as you go to virtual you loose some raw power because the box is busy running the virtual but more from the point of view that if we make cretin hardware resources available to the virtual would the server run the same as if it was running on a physical machine of the same specifications. For example, if 4gb ram and x amount of clock cycles where dedicated to the virtual would it preform the same as running it on a physical box 4gb ram and x amount of clock cycles. If the answer is no, any supporting documentation that anyone knows of would be greatly appreciated.
As a side note if it makes any difference the database data files and transaction logs are being sorted in an external SAN and not within the virtual image itself.
I have three systems that running different version of SQL sever.. 1. my notebook - SQL Server Express 2005 2. my WS - SQL Server 2005 Standard 3. my server - SQL Server 2000
I installed SSEUtil to all of them and only the notebook works.. I read the Read me but it doesn't help me resolve my problem so I hope someone from this forum can help me out..
The problem: Running sseutil on system 2. and 3. get this error, but not on 1. SSEUtil An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Additional information: The service 'MSSQL$SQLEXPRESS' is not installed.
