Why do database users with no associated login have access to a database?

Question

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?

Answer 1

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, G1 and G2.

You have 2 servers, S1 and S2 and every group is mapped to only one server: G1 is a login on S1, G2 is a login only on S2.

You have also a database db1 on S1 where G1 is mapped and has all the permissions on this db1 (let's say G1 is db_owner of db1).

Now you restore db1 on S2. 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 S1.

And now our account acc is logging on server S2. 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 db_owner of db1.

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.login_token and sys.database_token, so just impersonate that login (acc):

execute as login = 'dom\acc';
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;