Restore Database User to Server Login on SQL Server 2008

Question

We have a database backup we need to restore on a server that doesn't have the logins for the database... all the users are in the database though so we are hoping we can restore it this way?

I've played with sp_change_users_login but it only works with creating new users or linking orphaned database users to existing server users (mis-matched SID's but same usernames).

I need to create server logins from the user info stored in a database.

Answer 1

If your goal here is to recreate the the logins with the same password then you are SOL, this is only stored in the tables/views in the master database. Brian's recommendation will work if the old instance is still around but if not then you will need to do one of the options below.

• Recreate the logins
• Use sp_change_users_login to map the database user to the new login

or

• Create new logins
• Assign rights to that login in the restored database

Note that this is only the case with SQL logins, if you are using Active Directory logins then all you need to do is add them back to the server and they will automatically have rights in the restored databases because the password and SID are stored in AD and not in the master database for the instance.

Answer 2

I don't believe you have enough information about the users in the database to create the logins. The login-specific information resides in the master database, whereas the database user information (including mappings) resides in the individual databases.

If the original server is available, there is a system stored procedure, called sp_rev_login, that you can create from this Microsoft support article that will script out the login information, including the correct SIDs. You then can reconcile the logins to the users.

Answer 3

I have a nice script that you can use to create logins from database users, this script is using a stored procedure which you can find at the following URL: How to script logins from user database in SQL Server 2005

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

Answer 4

try

EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'AUTO_FIX', '<user name>', NULL, '<password>'

Where is one of the user name you find from first query and is new password you want to assign to user.

I shamelessly re-posting info I just found on - http://tim-stanley.com/post/how-to-restore-a-sql-user-after-a-sql-database-restore/ thought It will be useful for others.

Answer 5

this script worked in my DB

DECLARE @login nvarchar(50)

DECLARE logins_cursor CURSOR FOR

select master..syslogins.name as login_name

from master..syslogins 
WHERE master..syslogins.name<>'BUILTIN\Administrators'

OPEN logins_cursor
FETCH NEXT FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0
 BEGIN
    EXEC dbo.sp_change_users_login 'Auto_Fix' ,  @login
    FETCH NEXT FROM logins_cursor INTO @login
 END

CLOSE logins_cursor
DEALLOCATE logins_cursor
GO


select master..syslogins.name as login_name,
  sysusers.name as user_name
from master..syslogins LEFT OUTER JOIN sysusers
  on master..syslogins.suid = sysusers.suid

--NOTE: use-> master..syslogins.sid = sysusers.sid in sql2000

Answer 6

Create the logins and then use:

ALTER USER usr1 WITH LOGIN = login1;