Creating a stored procedure to fix orphaned database users

The Problem

This problem demonstrates a very common scenario. When a backed up database is restored to a different location or server, the restored database users that exist inside of it, lose their association to the new server login objects.

This problem occurs because the server id in the user data is still the old server and must be changed to the new one.

The DBA task is then to restore the connection between the login and the database user object and to link them together again.

Why does this Problem occur?

This problem occur because of the fact that when we are migrating the database to a new server, by using the BACKUP DATABASE and RESTORE DATABASE statements (or by other methods), the relationship between the login object and database user object can break.

The login object has a security identifier called SID, which uniquely identifies it on the specific SQL server instance. This SID column is placed, as a foreign key value, at the login’s associated database user, in each database that the login has access and is a member of.

Creating another SQL login that resides on a different SQL server instance and has the same name, will not, as one might expect, recreate the same SID.

How does the solution using traditional TSQL statements work?

In older versions of SQL server (e.g. 7, 2000, 2005 until SP1), the system stored procedure sp_change_users_login was used to re-associate the user with its original login.

From version SQL server 2008, the official books online help warn us that this stored procedure will be removed in future versions of SQL server and we should avoid using it in our new development work and start modifying applications that currently use this procedure to the ALTER USER statement.

Nevertheless, the procedure is used and works even on the new SQL server 2016 version.

The major disadvantages for using the sp_change_users_login procedure are that:

1. The sp_change_users_login procedure cannot be used with SQL server logins that are created from windows (the new syntax with ALTER USER can do it)

2. Microsoft constantly announces that this procedure might not appear in future versions of the product so one should not get used to relying on it too much.

Since of version SQL server 2005 service pack 2, the ALTER USER WITH

LOGIN statement can be used in order to remap login and user associations.

This applies to both the SQL and the window accounts which is can become very useful if the underlying windows user or group has been re-created in active directory and now has a security identifier that no longer maps to the SID on the SQL server instance.

This new syntax solves this problem and also complies with the ANSI-SQL standard for SQL statements. The newer ALTER USER WITH LOGIN clause enables the remapping of the user to a different login.

Users without a login, users mapped to a certificate, users mapped to an asymmetric key cannot use this clause. Only SQL users or windows users or group can be remapped. The WITH LOGIN clause cannot change the type of user, for example, changing from SQL user to windows user.

How does the solution work?

I have designed a rather simple stored procedure called OrphanDBUsersAutoFix that finds all the SQL users in a database that do not have an associated server login and reconnects them.

This is done by using an outer join between the sys.database_principals and the sys.server_principals catalog views on the mutual SID column.

Alternatively, sp_change_users_login could be used with the ‘REPORT‘ parameter. I preferred not to do it since it will be removed in future.

For every database SID that does not have a corresponding server SID, an

ALTER USER statement is done in order to re-create the association.

The procedure will have an input parameter called @mode with two possible values:

‘REPORT’ – will only report the orphaned database users but without actually associating them to their respective login object.

‘EXECUTE’ – actually do the association between the database user object and login object.

I want to add that in previous versions like SQL 2000, 2005 or 2008, the method to identify an orphaned database user was to query the sysusers system table inside the application database and verify that the issqluser column equals 1 and also that the SID column is sysusers table is not null but the suser_sname function on that SID equals to NULL.

Code example:

SELECT name
FROM sysusers
WHERE issqluser = 1 and	– The SQL User
(sid IS NOT NULL AND sid <> 0×0) AND	– The SID column in database level is not empty
suser_sname(sid) IS NULL	– The SID column in server level is empty

There are two assumptions that are being made:

Both the user and login objects should exist prior to the procedure’s execution.
The names of user and login are identical.

Here is the Procedure TSQL Code:

CREATE PROCEDURE [dbo].[OrphanDBUsersAutoFix] (@mode VARCHAR(10))

BEGIN

SET NOCOUNT ON

DECLARE @tsql VARCHAR(400)

DECLARE @username SYSNAME

DECLARE c_orphanedusers CURSOR

FOR SELECT a.NAME AS OrphUsr

FROM sys.database_principals a

LEFT OUTER JOIN sys.server_principals b

ON a.sid = b.sid

WHERE (b.sid IS NULL) AND (a.principal_id > 4) AND (a.type_desc = 'SQL_USER')

OPEN c_orphanedusers

FETCH c_orphanedusers

INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN

SET @tsql = 'ALTER USER ' + @username + ' WITH LOGIN = ' + @username;

IF UPPER(@mode) = 'EXECUTE'

EXEC (@tsql)

ELSE

IF UPPER(@mode) = 'REPORT'

PRINT (@username)

FETCH c_orphanedusers INTO @username

END

CLOSE c_orphanedusers

DEALLOCATE c_orphanedusers

IF (

SELECT count(a.NAME) AS OrphUsr

FROM sys.database_principals a

LEFT OUTER JOIN sys.server_principals b

ON a.sid = b.sid

WHERE (b.sid IS NULL)

AND (a.principal_id > 4)

AND (a.type_desc = 'SQL_USER')

) = 0

PRINT 'No Orphaned SQL Users.'

SET NOCOUNT OFF

END

Here are some explanations for the above code:

We iterate through a cursor that holds the entire orphaned database user names.

For each orphan user, a dynamic TSQL statement is constructed that does the association to the server login. (This is done only for SQL logins)

At the end of the procedure, a check is done that the count of orphaned users inside the database equals zero.

Here is an example for the procedure execution with some explanations:

In order to test the procedure, we will create an orphaned user called “sqluser”.

We will do it by first creating a login and a user. Afterwards we will drop the login and recreate it, leaving the user with no change, so there will be no association between the user and login objects.

This will test our procedure and see if it reports the orphaned login in the report mode and afterwards associate it to the appropriate login by using the execute mode.

Here is the example’s code:

USE Northwind;

-- create the login

CREATE LOGIN sqlUser

WITH PASSWORD = 'myTestSQL!', DEFAULT_DATABASE = Northwind;

-- create the user

CREATE USER sqlUser

FOR LOGIN sqlUser;

-- Now dropping the login object and re-creating it.

DROP LOGIN sqlUser;

-- re-create the login

CREATE LOGIN sqlUser

WITH PASSWORD = 'myTestSQL!', DEFAULT_DATABASE = Northwind;

You can verify it by executing the above procedure in the REPORT mode produces the result:

EXEC OrphanDBUsersAutoFix @mode = 'REPORT'

sqluser

After executing it and running the procedure with Report parameter it will produce the result:

EXEC OrphanDBUsersAutoFix @mode = 'EXECUTE'

No orphaned SQL users

Command(s) completed successfully.

This means that no orphaned users remain, so no user names are printed.

Here are some suggested improvements for the stored procedure:

Add a parameter called @usertype that can have SQL_USER or WINDOWS_USER values. Change the procedure source accordingly.
Check if the login exist and if not, create it according to user name.

Here are some relevant references

Here are some suggested references on the article’s subject, recommended for further reading:

An excellent post by Mr. Ted Kruger: "Fixing orphaned database users 2005 to 2012"
A good MSDN article: "Trouble shooting orphaned users in SQL server"
The system sp_change_users_login official reference

Author
Recent Posts

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and senior database consultant with 24 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science.He can be reached at: iecdba@hotmail.com

View all posts by Eli Leiba