If you are on SQL Server 2005 and up and you are still using sp_change_users_login (I know old habits die slow) then listen up, there is an easier way to fix permissions.

If you have a login on one server and you have the same login on another server and you did not use the sp_help_revlogin to create the login with the same SID then you have 2 options. drop and create the user(and apply all the permissions) or you can map the existing database user to a SQL Server login.

This is the old way

EXECUTE sp_change_users_login 'Update_One', 'UserName', 'UserName'

And here is the new way, much cleaner

ALTER USER UserName WITH LOGIN = UserName

Let’s write some code and see how this all works

First we are creating a new user named TestLogin

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Now it is time to create a new database, in this database we will create the user TestLogin

CREATE DATABASE TestLogin
GO

USE TestLogin
GO


CREATE USER TestLogin FOR LOGIN TestLogin
GO

Now we will backup the database

USE master
GO

BACKUP DATABASE TestLogin TO  DISK = N'c:TempTestLogin.BAK' WITH NOFORMAT, INIT,  NAME = N'TestLogin-Full', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

And now we can drop the database since we will create it again later anyhow

USE master
GO

DROP DATABASE TestLogin
GO

Since I want to show you the code so that you can run it on the same server, we will just drop and recreate the login

USE [master]
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TestLogin')
DROP LOGIN [TestLogin]
GO


USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Now, we will create the same database again with the same user

CREATE DATABASE TestLogin
GO

USE TestLogin
GO


CREATE USER TestLogin FOR LOGIN TestLogin
GO

USE master
GO

Go ahead and restore the backup we created before

ALTER DATABASE TestLogin SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE TestLogin FROM  DISK = N'c:TempTestLogin.BAK' WITH  FILE = 1,
NOUNLOAD,  REPLACE,  STATS = 10
GO


ALTER DATABASE TestLogin SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

We now will use this database and use the sp_change_users_login procedure to see if any SIDs are mismatched

USE TestLogin
GO

EXECUTE sp_change_users_login 'report'

On my machine, I get the following back

UserName   UserSID
TestLogin   0x7ED6E205155E9C40BA684E72453BAE1B

We can easily test this because if you try to login as that user and then execute the command below you will get an error message

USE testlogin
GO

Msg 916, Level 14, State 1, Line 1

The server principal “TestLogin” is not able to access the database “TestLogin” under the current security context.

Leave that query window open for now, we will get back to it later.

Now let’s fix the user by mapping the user to the login

EXECUTE sp_change_users_login 'Update_One', 'TestLogin', 'TestLogin'

Now this query doesn’t return any rows since the user has been fixed

EXECUTE sp_change_users_login 'report'

Now refresh the query or connect again and run this

USE testlogin
GO

As you can see it is fine now

Disconnect from the DB with the TestLogin account and then drop the database

USE master
GO

DROP DATABASE TestLogin
GO

We will create the database again, create the user again and finally we will restore the database

USE master
GO

CREATE DATABASE TestLogin
GO

USE TestLogin
GO


CREATE USER TestLogin FOR LOGIN TestLogin
GO

USE master
GO

ALTER DATABASE TestLogin SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE TestLogin FROM  DISK = N'c:TempTestLogin.BAK' WITH  FILE = 1,
NOUNLOAD,  REPLACE,  STATS = 10
GO



ALTER DATABASE TestLogin SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Just as before, we are getting back the mis matched SID

USE TestLogin
GO

EXECUTE sp_change_users_login 'report'

UserName   UserSID
TestLogin   0x7ED6E205155E9C40BA684E72453BAE1B

You will get the same error from before if you try to connect to this database

USE testlogin
GO

Msg 916, Level 14, State 1, Line 1

The server principal “TestLogin” is not able to access the database “TestLogin” under the current security context.

Here is how to do the same thing with ALTER USER as with sp_change_users_login

ALTER USER TestLogin WITH LOGIN = TestLogin

As you can see, this doesn’t return anything anymore

EXECUTE sp_change_users_login 'report'

So, start using ALTER USER UserName WITH LOGIN = UserName instead of sp_change_users_login to fix the mappings, sp_change_users_login is on the endangered deprecated list and will be removed in a feature versions.