I have a question regarding security in MS SQL Server (right now, I'm trying this on a SQL 2016).
Scenario:
User AD\testuser
is in both AD security groups (AD\testgroup1
and AD\testgroup2
).
- A new database
testDB
is created. - A new table
Testtable
is created intestDB
. - Login is created for
AD\testgroup1
- User
AD\testgroup1
is created in databasetestDB
- User
AD\testgroup1
haveselect
permission on tabletestTable
.
We log in as AD\testuser
, user only has select on TestTable
–> as expected
- Login is created for
AD\testgroup2
- User
AD\testgroup2
is created in databasetestDB
- User
AD\testgroup2
granted forselect, insert, update on table
testTable
.
We log in as testuser
, user has select
, insert
, update
on TestTable
–> as expected
Login AD\testgroup2
is removed from server (so AD\testgroup2
is still a use rin the database, but login is removed)
We log in as testuser
, user has select
, insert
, update
(we were under the impression this should only be select
)
Access for user AD\testgroup2
is revoked in database testDB
We log in as testuser
, user has select, insert, update
(we were under the impression this should only be select
)
User AD\testgroup2
is dropped in database testDB
. When we test with same user, user only has select
–> as expected
Why do we still get the highest permissions, even when AD group is removed from logins, and disabled in DB users?
Best Answer
This is because
testuser
is a member of bothAD\testgroup1
andAD\testgroup2
and both those groups exist in the database with object permissions, resulting in cumulative object permissions for thetestuser
role member.There is no requirement that a login exists for a Windows group database user; one can add a Windows group database user and grant permissions to that user regardless of whether a login for the Windows group exists. Permissions granted to groups will be honored for role members even if a corresponding login doesn't exist, as you've observed.
Without an individual login for
testuser
, the user must be a member of at least one Windows group with a login to connect to the server. This wasAD\testgroup1
in your example after you dropped theAD\testgroup2
login. But the user still had object permisssions granted toAD\testgroup2
until you dropped the user.