Restore Database User to Server Login on SQL Server 2008
Solution 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.
Solution 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.
Solution 3
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.
Solution 4
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
Related videos on Youtube
sontek
Updated on September 17, 2022Comments
-
sontek almost 2 years
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.