May 3, 2007 at 3:51 pm
I have a C# program that tries to programatically execute a stored procedure in a SQL Server 2005 database. I get a permissions failure error (sorry I don't have a screen cap. The subject machine is in another building.)
The connection string in the code calls for Integrated Security. I am listed as a user in SQL Server with owner as well as read and write roles. I am logged into Windows under the same user login.
I don't understand why the permissions are failing. I cannot add myself as a user directly in the db - I get the error "The login already has an account under a different user name."
I can duplicate the error in SQL v8 by disabling db access to my windows account name. Oddly enough, on my development machine, my windows user in SQL doesn't even have access to the subject database checked - I wouldn't think my programs would work like that.
I don't know if I've described this well enough for anyone to help. But if someone could take a stab at it, I'd surely appreciate it.
Thanks.
=====================================================
I have an update. I modified my software to use a specific user and password. Again, it worked on my development machine, but not on the SQL 2005 machine. This is the error I'm getting when I try to execute the stored procedure:
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
It's the same error as before. Does that help any?
May 4, 2007 at 11:21 am
You didn't mention what level of access you have to the database in question. If you're not mapped to the owner of the stored procedure (for example, 'dbo'), then your login, role or schema needs permission to execute the stored procedure:
GRANT EXECUTE ON spWhatever TO RoleName
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply