Category Archives: How to remap users to existing Logins in SQL Server.

map database users to existing logins in SQL Server.


Orphan Users and Logins: Fixing orphan users :Mapping the users to existing logins in SQL Server 2005

Now we will have a better understanding of the problem, it is time to get to some useful commands for analysis and a solution.

I have restored the AdventureWorks database from one instance to another with the four users. Now to analyze how many orphaned users there are in my restored database, I will run the following T-SQL command which produces a listing of all the orphaned users and in our case all four users are orphaned.

——Command to generate a list of Orphaned users in a database.———–

EXEC sp_change_users_login @Action=’Report’
GO

By executing the above query we will get the list of Orphaned users in respective database.

Run below T-SQL statement to autofix the orphaned users.

———-Command to map an orphaned user———-

EXEC sp_change_users_login ‘Auto_Fix’, ‘TestUser2’
GO

———-Command to map an orphaned user to a login that is not present but will be created——

EXEC sp_change_users_login ‘Auto_Fix’, ‘TestUser3′, null,’pwd’
GO

Summarizing the T-SQL Used:

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

Some considerations:

 
• sp_change_users_login requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
• When the orphaned user is mapped, the SID in the master database is allotted to orphaned user, so every time a DB is attached or restored the SID will differ between SQL Server login and database user.
• If you have different Server login names mapped to a database user then do not use the command with Auto_Fix for linking
• A user may become orphaned also if the corresponding SQL Server login is dropped
• Although obvious, but it is good to mention, after re-linking the password of SQL Server login can be used by the database user.

Next Steps

 
• Now that you have a better understanding of this issue and how to resolve it using sp_change_users_login, it would be a good practice to prepare a script for detection and linkage of orphaned users for all of your databases.

For auto fixing and auto mapping the database users to existing sql logins, try executing the below T-SQL Statements which creates a procedure and by executing the procedure, the database users are automatically mapped to the existing logins.

**********************************************************************************************

How to remap users to existing Logins in SQL Server:

/*********************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/

IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<>>’
ELSE
PRINT ‘<<>>’
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<>>’
ELSE
PRINT ‘<<>>’
go

*************************************************************************************

Modified this script to handle the ‘dbo’ user. So yo no longer have to run
sp_changedbowner ‘sa’ before (Or after) running this proc.

*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/

IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<>>’
ELSE
PRINT ‘<<>>’
END
GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
IF @username=’dbo’
BEGIN
EXEC sp_changedbowner ‘sa’
END
ELSE
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<>>’
ELSE
PRINT ‘<<>>’ go

***********************************************************************************************

Have a happy mapping….!!

Advertisements
%d bloggers like this: