Sql-server – Why different users from same login group can not access the database objects

sql serversql-server-2008sql-server-2008-r2

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??

Best Answer

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.

Related Question