Update Orphaned SQL Users

SQL Script to relink orphaned users to logins

It's quite a common task to have to move one or more databases from one server to another and this usually causes issues with orphaned users that have no related SQL login due to the SID values mismatching. The following is taken from an MS Knowledge base article about the problem:

When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time.

Therefore the issue becomes how to sync up the logins in the copied database that relate to Login SID's that no longer exist on the server. If you try to manually re-save the login and grant it access to the copied database you are likely to run into problems with an error that prevents you from accomplishing the task such as:

User or role already exists in the current database

Therefore you either have to run a script to delete the users and then re-create them all correctly or do as I did and use some SQL to output the necessary TSQL statements that use the system views sys.sql_logins and sysusers and the stored procedure sp_change_users_login to relink the orphaned user to an SQL login.

-- example output to relink logins to users USE myDB1 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase101', 'mydatabase101' GO USE myDB2 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase_admin_101', 'mydatabase_admin_101' GO

The script has two options which allows you to either just output a list of all the orphaned users and their related databases so that you can run any reconfigure statements yourself. The other option will output all the necessary TSQL statements to relink the user to an SQL login of the same name.

IF @MODE = 'SQL'
BEGIN
 -- loop each orphan and generate auto fix code

 SELECT @MaxRow = COUNT(*), @RowID = 0
 FROM #RESULTS

 IF @MaxRow > 0
   BEGIN
  WHILE @RowID > @MaxRow
    BEGIN
   SELECT @RowID = @RowID + 1

   SELECT @DatabaseName = DatabaseName
    ,@OrphanedUser = OrphanedUser
   FROM #RESULTS
   WHERE RowID = @RowID

   -- output the TSQL to relink the user with the login

   SELECT @SQL =  'USE '   + @DatabaseName + + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)
      +'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @OrphanedUser + ''', ''' + @OrphanedUser + '''' + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)

   PRINT @SQL
    END
   END

END
ELSE -- output the orphans
BEGIN
 SELECT *
 FROM #Results
 ORDER BY [DatabaseName], [OrphanedUser]
END

Obviously this is no good if you use different names for your logins and their corresponding users but as I don't it was perfect for me and enabled me to quickly resolve all the issues I had today when moving some large and important databases to a new server.

The code specifically ignores certain system users as well as all Windows Logins. It is specifically for SQL 2005 and above and is based on an SQL 2K script I found at the following site:  vyaskn.tripod.com. 

If you require a script for an SQL 2K or SQL 7 server then I suggest checking out that site as this script will not work due to changes in the SQL System Views it uses.

To get the full source code please visit the following link:

Download the SQL

By Srictly-Software

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Problems with database ownership SID values

I recently came across a problem which I have had a couple of times now which seems to be related to database backups in SQL Server 2005.

I was carrying out a manual backup of a database on the production server which was taking a very long time. During this process I came across another problem which needed solving quickly and I couldn't wait until the backup had finished before trying to resolve the issue which was caused by a report called from one database trying to access data from another. It was raising the following error message:

The server principal "XXXXX" is not able to access the database "XXXX" under the current security context.

The solution to this problem is to enable the database that needs to be accessed as Trustworthy with an SQL statement like this:

ALTER DATABASE strategies_jobboard SET TRUSTWORTHY ON

So I ran this statement to fix the reporting issue and I had some hesitations about doing this as I didn't know whether the combination of the backup and the lock on the Database would cause any issues and low and behold just after running this statement I started receiving the following errors:

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'. You should correct this situation by resetting the owner of database 'XXXX' using the ALTER AUTHORIZATION statement.

Now this is an error I have come across before when a database has been created on one server by restoring a backup taken from another server and the database owners differ. To prove that the problem is in fact differing SID's I ran the following two SQL statements.

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

They should return you SID values in the format of a GUID e.g

0x010500000000000515000000BF76FC16EA335FE078A67D48FB030000

Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g

ALTER AUTHORIZATION ON Database::XXXX TO [domain\user]

Once I had run this code the problem was fixed. Now I am pretty sure the issue was down to a combination of the backup running at the same time as the code to change the TRUSTWORTHY statement as I had no errors before that SQL was run and as soon as I had executed it the errors started shooting into my inbox.

However I tried to replicate this problem on a development server by running some backups and executing the same SQL but I couldn't re-create the problem. Maybe there was something else in the mix I was unaware of but I thought I would post the solution to the problem anyway in case others have a similar situation arise.