« Windows Live Mail Log FileOracle 10g XE (Express) Web Interface Remote Access »

Fixing Orphaned SQL Server Users

02/16/09

Permalink 07:53:49 pm by guy, Categories: Windows, MS SQL , Tags: sql server

If you restore a database from one SQL instance to another you will often run into the issue where your logins for the restored database are no longer properly mapped to the new SQL Server instance’s users. Even if the users exist on the new server they probably won’t be mapped properly. This is scary, but easy to fix in most cases. This is documented all over the place, but I’m putting it here for me.

If you already have the login in your destination SQL Server, but they are not matched up then change to the restored database and execute the following sp:

USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Bob'
GO

In this case you have restored Adventureworks to a new instance and you have a ‘Bob’ login account, and the database has a ‘Bob’ user, but they are not mapped. The command above we automap it with the assumption that the login and the user are identical.

You can map to another, non-matching account using this command:

USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Bob', 'BobsLogin'
GO

If you do NOT have a Login user an you want to create one in a single step then do this:

USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Bob', NULL, 'sUperD00perPassw0rd';
GO

This automatically creates the missing login account matching the specified user and sets the password to the specified value.

http://technet.microsoft.com/en-us/library/ms174378.aspx
http://www.mssqltips.com/tip.asp?tip=1590

July 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
            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 28 29
30 31          
I'm a generalist, at least if I'm honest. In my job I am primarily a developer, but also a sysadmin, and (as little as possible) technical support. I know a little about a lot of things, a lot about some things, and everything about nothing. Here I will post random learnings...

Search

XML Feeds

User tools

blogsoft