0

I have SQL login group as "SC_NT\xxxx" in server. There are multiple users using this login group to access database objects. User "A" can able to access db objects from .net application. But when user "B" tried to access same db objects, its showing like

Error: The EXECUTE permission was denied on the object 'stored_procedures', 
database 'test',schema 'dbo'. 

Both the users are using windows authentication for access the objects. Kindly give me some clear idea to fix this issue??

migrated from stackoverflow.com Nov 25 '14 at 8:31

This question came from our site for professional and enthusiast programmers.

2

2 possible reasons:

  1. User A receives necessary permissions via his membership in some other group, or explicitly being a member of server / database role, or has permissions granted directly.
  2. User B is denied some permissions, again - either via his membership in some other group, or explicitly being a member of server / database role, or has these permissions denied to him directly.

You can compare their role memberships on the database / server level, but membership in Windows groups cannot be checked via SQL Server - you would have to look in local groups AND Active Directory, if the machine is a member of a domain.

EDIT: here is a set of queries that may help you track down actual origins of permissions. Just make sure that you run them under the user with appropriate credentials, otherwise you will never see anything. Membership in sysadmin fixed server role is preferable.

declare @ObjectName sysname = N'YourObjectName',
    @UserName sysname = N'YourUserName';

-- Grants on database securable
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    inner join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where dp.class_desc = 'OBJECT_OR_COLUMN'
    and ao.name = @ObjectName;

-- Direct permissions of database principal
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    left join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where pr.name = @UserName;

-- Membership in database roles
select rl.name as [DatabaseRole]
from sys.database_role_members rm
    inner join sys.database_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.database_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

-- Membership in server roles
select rl.name as [ServerRole]
from sys.server_role_members rm
    inner join sys.server_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.server_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

The only thing not covered here is a membership in Windows / Active Directory security groups, but, as I've said, it is impossible to track it within SQL Server.

  • What ever you have suggested for me, thats makes sense. But still i'm not sure. So let me compare the memberships from other group and try to find the solution. Thanks Man – Venkat G Oct 8 '14 at 11:58
  • Hi Roger, I have checked other groups for the neccessary permissions. But none of the groups are giving access to the particular stored procedure. Stil User A can able to execute that sp. But User B not able to execute it. Do u think any other possiblities for this issue?? – Venkat G Oct 9 '14 at 5:15
  • Is it possible that User A owns the database (or schema in which the procedure resides)? – Roger Wolf Oct 9 '14 at 6:32
  • @VenkatG, I have added some queries to my answer, should be of some help. – Roger Wolf Oct 9 '14 at 7:44
  • my particular stored procedure has default "dbo" permissions only. I am able to execute this sp in sql server without any issues. But whenever i tried to execute from .net application as a user B, i am facing the issue. So here i dont think any schema problems are there. Its driving me nuts... – Venkat G Oct 9 '14 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy