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
CREATE LOGIN wsxcde1 with password ='wsxcde1'
--create a test database to play with
CREATE DATABASE TESTING
--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]
EXEC sp_addrolemember N'db_datareader', N'wsxcde1'
--Create a custom role and add the user to this role and Grant Read permissions to the role.
Create Role myCustomRole
EXEC sp_addrolemember N'myCustomRole', N'wsxcde1'
EXEC sp_addrolemember N'db_datareader', N'myCustomRole'
--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
Drop database testing
Drop login wsxcde1