My understanding of SQL security is such that in order to have access to the server, you need a login. To access a database on that server, your login needs to be associated with a user in that database. Over time I have been consolidating security so that developers, QA, etc. are in various roles implemented as active directory groups. Those groups have logins on the server, so individual windows logins are not so prevalent on SQL instances. I grant proper accesses to the databases through these roles.
A common service I provide is to refresh non-production versions of databases from production. I’ll do a backup and restore of a production database onto a development server, de-identify the data, rename it, etc.
I have noticed something recently in my environment that is troubling. I was granting access to a key table in production for a development team, when I noticed by using ‘fn_my_permissions’ that one of the developers has read and write access to the entire production database, rather than being restricted to read-only as I designed. I compared all of this developer’s group memberships and found another group he was a member in that was assigned read/write – but only as a database user. There is no associated login for that group on that SQL server.
It is typical here to copy a ‘baseline’ production database and repurpose it elsewhere – whether as another production database, or a non-production version. I am starting to find other examples of this anomaly. To sum it up, the problem I see is that when a database is copied to another server, the users in that database that were previously linked to logins on the original SQL instance seem to retain the same level of access on the new SQL instance – even if there is no related login for them on new SQL instance! This strange effect seems limited to users representing windows group logins.
Here is a summary of what I am seeing:
Server1 has a login tied to windows group A, which has been granted read/write to database X.
Database X is then backed up and restored onto Server2, with logins but none related to group A. The database still shows a user for group A, and when I run ‘fn_my_permissions’ for a user in that group he has read/write to the database.
This particular server is running SQL Enterprise 2012 SP3 with the latest CU. I’ve seen the same thing on servers running 2008 R2, as well as a current build of SQL 2016.
Why is this happening? And what can I do to ensure that users do not inadvertently circumvent the controls I have put in place? It seems obvious I need to remove all of the unnecessary users after the databases are copied, but why?
It seems obvious I need to remove all of the unnecessary users after
the databases are copied, but why?
Yes you should remove all the unnecessary users, at least Windows users. And that is why:
For every SQL Server login when creating it, server assings a certain sid(security identifier) that is stored on the server. Every time you map this login to some database, a user with the same sid is created in a database. When you restore this database on another server, unless someone recreates the login with the same sid, it will never reach your database.
Even if the login with the same name already exists on another server, the login’s sid is different from user’s sid that was restored, so you need to fix this orphan user altering it with corresponding login, to make user’s sid equal to corresponding login sid.
The important thing here is that every SQL Server login has only one
sid associated with it, and it has access to database only if this sid is also mapped to a database.
Windows logins are different. Server does not create sids for them, it uses Windows access tokens.
When Windows account tries to log on, its sid does not need to be found in
sys.server_principals. Instead, any of sids from access token will work as a pass.
Access token contains not only the security identifier (SID) for the user’s account but laso SIDs for the groups of which the user is a member.
This means that a situation like yours can be created:
Imagine Windows account
acc is a member of 2 Win groups,
You have 2 servers,
S2 and every group is mapped to only one server:
G1 is a login on
G2 is a login only on
You have also a database
G1 is mapped and has all the permissions on this db1 (let’s say G1 is db_owner of db1).
Now you restore
G1 is still db_owner of
db1, but you know that you have no corresponding login on
S2, Win group
G1 is not mapped to
S2, so you think for now you have no db_owners of db1 as the only db_owner was
G1 but it cannot reach the server
And now our account
acc is logging on server
It can log on to server
S2 because it’s a member of
G2 group. You think that it cannot reach
db1 because login
G2 is not mapped to
db1, but surprise,
acc can reach
db1 and it is
That is because
acc is a member of
G1. Its token has not only its own SID, it has
G1 sid and
G2 sid, so
G2 sid was used to access a server, and
G1 sid was used to access a database.
You can find all the tokens in
sys.database_token, so just impersonate that login (acc):
execute as login = 'domacc'; select * from sys.login_token where principal_id > 0 -- that is to see only those mapped to server use db1; select * from sys.user_token;