Deny vs Grant – which one supersedes?

There can be situations where a database user is part of multiple groups and if one of the group the user is part of has deny permissions and the other group has grant permissions on a object – which permissions would go through?

In common sense, you would think deny should supersede the grant permissions, because you want to have preventive protection and SQL Server works in the same way. Deny will override the Grant permissions.

Below sample code will demonstrate the same:
--create a test login
USE Master
GO
CREATE LOGIN wsxcde1 with password ='wsxcde1'
--create a test database to play with
CREATE DATABASE TESTING
GO
USE Testing
GO
--Create sample table
Create Table Test(sno int)
Create table Test2(sno int)

--Create user for the logins and assign data reader role for the users.
CREATE USER [wsxcde1] FOR LOGIN [wsxcde1]
GO
EXEC sp_addrolemember N'db_datareader', N'wsxcde1'
GO
--Create a custom role and add the user to this role and Grant Read permissions to the role.
Create Role myCustomRole
GO
EXEC sp_addrolemember N'myCustomRole', N'wsxcde1'
GO
EXEC sp_addrolemember N'db_datareader', N'myCustomRole'
GO

--Deny select permissions on table test2 to the custom role
Deny select on test2 to mycustomrole

At this point, database user wsxcde1 has read permissions on the database directly as the user itself and indirectly through the database role. However, the user was denied permissions on table dbo.test2 explicitly for the custom role.

--Now check if select will go through
Execute as user ='wsxcde1'
Select * from dbo.test2
Select suser_name()
Revert
--Clean up
USE Master
GO
Drop database testing
Drop login wsxcde1

Advertisement

Users associated with database roles.

Below code lists all the database users that are associated with all the roles present in the database.

select B.Name as [Database Role],C.Name as [Database UserName]
from sys.database_role_members A
INNER JOIN sys.database_principals B on B.principal_id=A.role_principal_id
INNER JOIN sys.database_principals C on A.member_principal_id=C.principal_id

Assigning Permissions to Individual Users from Group Logins

Let’s say we have Developer’s Group and one developer in that group needs higher permissions than others on one database.
In that case instead of creating dedicated login for the user, we can extract the login from the group and assign the required permissions on the database.

For example: assume company ABC has developer group and all developers have same permissions except that one senior developer needs higher privileges on one database.

Dev. Group Login: ABC\Developers and senior Developer: ABC\Rose

ABC\Rose authenticates herself as the part of the Dev. group on Server level and on the database level, you can create a database user and associate it with the login ABC\Rose.

The login ABC\Rose does not explicitly exist on the server but as a part of the DEV Group.

The way this works is that SQL Server knows that connection came from ABC\Rose and ABC\Rose is part of Developer group. So, ABC\Rose is authenticated on the server level and granted access. On the database level, we have to create a user for login ABC\Rose and this lets ABC\Rose to have access to the database with more permissions granted for the user.

Use [Master]
GO
Create Login [ABC\Developers] from windows WITH DEFAULT_DATABASE=[master]
GO
USE [DatabaseName]
GO
CREATE USER [ABC\Rose] FOR LOGIN [ABC\Rose]
GO
EXEC sp_addrolemember N'db_dataWriter',N'ABC\Rose'