September 11, 2012 at 10:40 am
We have a user account that is a member of an AD group, the latter granted access to a DB on a SQL Server 2K8 instance as a user mapped to that login, the user is a database rolemember, and that role is granted EXECUTE on a stored procedure. Perhaps this is clearer:
Domain\WebAccountName
- member of Domain\GroupName
-- Domain\GroupName logs into SQL Server
--- accesses DB as user: GroupName
---- GroupName is a member of RoleX
----- RoleX has EXEC on SprocA
I can do an EXECUTE AS Domain\WebAccountName on SprocA, but not EXECUTE AS WebAccountName (without Domain\), which is what I expect.
But we keep getting a execute permission denied on SprocA when calling it from IIS where the IIS user = Domain\User.
Any ideas?
September 21, 2012 at 9:55 am
The EXECUTE AS in a proc definition expects a database user, not a login. The database user has probably been named domain\accountname in the database. You can check this in SSMS or by querying sys.database_principals in the database.
Regarding the permission denied exception; Do you use integrated security i the connection string? If so, do you expect the proc to be executed in the context of the authenticated user on the client PC, or is it using the IIS App Pool account? If you are using integrated security, and expect the account on the client PC to execute the proc you need to make sure that Kerberos is set up correctly.
I would start by running a trace on the SQL Server to see who is trying to execute the proc.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply