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

T-SQL
1
EXECUTE sp_change_users_login 'Update_One', 'UserName', 'UserName'
EXECUTE sp_change_users_login 'Update_One', 'UserName', 'UserName'

And here is the new way, much cleaner

T-SQL
1
ALTER USER UserName WITH LOGIN = UserName
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

T-SQL
1
2
3
4
USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
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

T-SQL
1
2
3
4
5
6
7
8
9
CREATE DATABASE TestLogin
GO
 
USE TestLogin
GO
 
 
CREATE USER TestLogin FOR LOGIN TestLogin
GO
CREATE DATABASE TestLogin
GO

USE TestLogin
GO


CREATE USER TestLogin FOR LOGIN TestLogin
GO

Now we will backup the database

T-SQL
1
2
3
4
5
USE master
GO
 
BACKUP DATABASE TestLogin TO  DISK = N'c:TempTestLogin.BAK' WITH NOFORMAT, INIT,  NAME = N'TestLogin-Full', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
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

T-SQL
1
2
3
4
5
USE master
GO
 
DROP DATABASE TestLogin
GO
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
CREATE DATABASE TestLogin
GO
 
USE TestLogin
GO
 
 
CREATE USER TestLogin FOR LOGIN TestLogin
GO
 
USE master
GO
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

T-SQL
1
2
3
4
5
6
7
8
9
10
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
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

T-SQL
1
2
3
4
USE TestLogin
GO
 
EXECUTE sp_change_users_login 'report'
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

T-SQL
1
2
USE testlogin
GO
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

T-SQL
1
EXECUTE sp_change_users_login 'Update_One', 'TestLogin', 'TestLogin'
EXECUTE sp_change_users_login 'Update_One', 'TestLogin', 'TestLogin'

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

T-SQL
1
EXECUTE sp_change_users_login 'report'
EXECUTE sp_change_users_login 'report'

Now refresh the query or connect again and run this

T-SQL
1
2
USE testlogin
GO
USE testlogin
GO

As you can see it is fine now

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

T-SQL
1
2
3
4
5
USE master
GO
 
DROP DATABASE TestLogin
GO
USE master
GO

DROP DATABASE TestLogin
GO

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
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

T-SQL
1
2
3
4
USE TestLogin
GO
 
EXECUTE sp_change_users_login 'report'
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

T-SQL
1
2
USE testlogin
GO
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

T-SQL
1
ALTER USER TestLogin WITH LOGIN = TestLogin
ALTER USER TestLogin WITH LOGIN = TestLogin

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

T-SQL
1
EXECUTE sp_change_users_login 'report'
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.