Sql-server – Why do database users with no associated login have access to a database

permissionssql server

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?

Best Answer

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;

Related Solutions

Sql-server – Domain group as single login, but still many different database users

First of all, the way you are wanting to do it won't really work. You can't have one login and multiple users on a single database. If you try you get the following error:

Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.

What you really should do is create your generic group and create a login and users for it. This will provide the basic permissions for everyone. Next create an AD group for each country you are dealing with. Add your individuals to these groups. Then create logins and users for those groups providing them the individual access you want.

The benefit here is that if someone leaves the company all you have to do is move them out of the AD group and move someone else in. Or if a second person (or more) has to be added to a country it's a trivial thing.

EDIT: For example:

AD Group : DOMAIN\Sales
- Users: Bob and Joe
AD Group: DOMAIN\Sales_US
- User: Bob
AD Group: DOMAIN\Sales_Canada
- User: Joe

All three groups have SQL Logins created All three groups also have a user created on the Sales database.

The DOMAIN\Sales group is granted SELECT access to tables Inventory and InventoryPricing The DOMAIN\Sales_US group is granted SELECT permission on the view vw_Sales_US and execute on the stored procedures usp_Add_US_Sale and usp_Edit_US_Sale The DOMAIN\Sales_Canada group is granted SELECT permission on the view vw_Sales_Canada and execute on the stored procedures usp_Add_Canada_Sale and usp_Edit_Canada_Sale

This gives Bob access to the US information and the shared information and Joe access to the Canada information and the shared information.

Later on down the line Mary replaces Bob on US sales and since Canada sales have increased dramatically Bob has joined Joe on Canada sales.

The only changes that need to be made are removing Bob from DOMAIN\Sales_US and adding Mary and adding Bob to DOMAIN\Sales_Canada

Sql-server – Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication

At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.

At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.

Best Answer

Related Solutions

Sql-server – Domain group as single login, but still many different database users

Sql-server – Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication

Related Question