Friday, 29 January 2010

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:


3 comments:

Anonymous said...

I have also found an interesting script to re-link Orphaned users this script uses a stored procedure.

USE MyDatabaseName

DECLARE @login nvarchar(50)

DECLARE logins_cursor CURSOR FOR SELECT l.name FROM sys.database_principals u INNER JOIN sys.server_principals l ON u.sid=l.sid

OPEN logins_cursor FETCH NEXT FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_help_revlogin @login FETCH NEXT FROM logins_cursor INTO @login END

CLOSE logins_cursor DEALLOCATE logins_cursor

GO

Here is the url where you can also find a script from Microsoft to generate the T-SQL script to recreate a login.

http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/

Rob Reid said...
This comment has been removed by the author.
Rob Reid said...


Hi I don't seem to have the stored proc sp_help_rvlogin in my SQL 2012 database.

Are you sure it's not sp_helplogins?

When I replace sp_help_rvlogin with sp_helplogins I get an output of the SID for the main DB login and then details of each login and username for the current database (excuse the formatting - Googles comment box doesn't allow much room for decent formatting of tabbed data) e.g (fake login/user details obviously)

LoginName SID DefDBName LangName AUser ARemote
strictly 0x91F0B049AB5..... strictly British yes no

LoginName DBName UserName UserOrAlias
strictly strictly db_datareader MemberOf
strictly strictly strictly User

So I am not sure what use that is apart from viewing the info about logins and users as the other script will create the SQL needed to automatically re-link orphaned users with logins as long as they have the same name. So unless there is a different stored proc to use than sp_helplogins?