Keeping orphans away from your servers

When restoring a database backup to a different server it is wholly possible that there will be security issues when you take the next step and make the system live to the user interface.

Most often in our environment this happens when a live database backup is used to 'refresh' the data in the test system. To do this we take a backup file made from the database on Server A and restore it onto Server B. This also applies however if you have to restore to a new server in the event of a disaster - imagine Server A is on fire and you are under an SLA to get the system live again. First, make sure someone deals with the fire, preferably a trained professional such as someone from the local fire service.

In live the server may have a mixture of users - Windows domain user and group accounts, SQL server logins etc and some of these may have been given permission to the database you are in the process of transferring to a new location. This may not compare exactly with the new server configuration. In order to ensure that once the database is live you move on to dealing with all the security issues that may arise from the above scenario you need to reference the database security and the (new) server security details.

In SQL 2008 Database user details are stored in sys.database_principals; Server login details are stored in sys.server_principals.

In SQL 2005 Database user details are stored in sysusers; Server login details are stored in sys.syslogins.

The script below can be switched between the two versions simply by swapping the table names accordingly as the columns used are in both versions.

In order to view the users that have come with the database and dont exist on the new server we need to find them with:

SELECT
[dp].name AS [Orphan DB User]
FROM
[sys].[database_principals] AS dp
LEFT JOIN sys.[server_principals] AS sp
ON dp.[name] = [sp].name
WHERE
( [dp].[sid] IS NOT NULL
AND [sp].[name] IS NULL
)
AND CAST([dp].[sid] AS BIGINT) < 0

These users are in the database but dont have a server security principal to link to. Thats the "LEFT JOIN" and "[sp].[name] IS NULL" working it out for us.

Next are the database users that might have similar named principals on the server but if the SIDs for them dont match SQL Server will consider them different accounts and stop access to the database. This is a great GOTCHA - using SSMS you see the server login and then in the database you see the user, apparently identical. Only when you compare SIDs are the differences there to be seen. These occurrences are found with:

SELECT
[sp].[name] AS [SQLServerLogIn] ,
[sp].[SID] AS [SQLServerSID] ,
[dp].[sid] AS [DatabaseSid]
FROM
sys.server_principals AS sp
INNER JOIN [sys].[database_principals] AS dp
ON
sp.[name] = dp.[name]
WHERE
[dp].[sid] <> sp.sid
AND [sp].[sid] < 1

Now this is OK but we can combine the two queries to have a concise list of security changes that we need to review:

SELECT
NULL AS ['Split' login] ,
NULL AS [Server Principal SID] ,
[dp].[sid] AS [DB Principal SID] ,
[dp].name AS [Orphan DB User]
FROM
[sys].[database_principals] AS dp
LEFT JOIN sys.[server_principals] AS sp
ON dp.[name] = [sp].name
WHERE
( [dp].[sid] IS NOT NULL
AND [sp].[name] IS NULL
)
AND CAST([dp].[sid] AS BIGINT) < 0
UNION ALL
SELECT
[sp].[name] ,
[sp].[SID] ,
[dp].[sid] ,
NULL
FROM
sys.server_principals AS sp
INNER JOIN [sys].[database_principals] AS dp
ON
sp.[name] = dp.[name]
WHERE
[dp].[sid] <> sp.sid
AND CAST([sp].[sid] AS BIGINT) < 0

We filter out SQL Server roles and system principals such as Guest, sa, public etc by CASTing the SIDs to a BIGINT and checking its a -ve value.


Next time we will review what to do with these accounts and how to get them enabled for the database in its new location.