June 30, 2013 at 6:09 pm
we want to Transfer The Logins and The Passwords Between Instances of SQL Server 2008
we have 78 logins and 78 database users
what are the clear step by step of going by moving all the logins and users and fixing orphan users in concise manner
thanks
July 1, 2013 at 12:19 am
Hi!
To do this you need to use the SP_HELP_REVLOGIN script. It is a stored procedure which (once installed) upon running generates a complete list of logins with (for sql authenticated accounts) their associated passwords & SIDs. You then run the resulting script on other servers and the accounts are generated for you; by copying the SID you remove the problem of orphaned users.
Note that the script is not installed by default, you will need to add it
You can find some extra help on this at http://support.microsoft.com/kb/918992, additionally searching for 'SP_HELP_REVLOGIN' will return some good links.
Hope this helps,
James
July 1, 2013 at 12:16 pm
thanks james.bonnyman,
i have run the scripts and there are always many mismatches and that is better but its not 100% solution.
when you said install scripts what you mean?
July 1, 2013 at 12:38 pm
Having used this script: When you first run the SQL provided by MS, it creates a Stored Procedure (or if you prefer, installs one)
As for mis-matches, what may be happening is you're running the script to create the logins before the DBs are there. I've found that if a login has its default DB set to a DB that isn't there, it won't be created. So the way to avoid that is to restore the DBs, then run the create login script that was generated on the source server.
If you're moving a couple DBs at a time, then just either search the create login script for the ones you need, or re-run the whole thing every time and ignore the "can't create user [jimmyjoebob] because the user already exists" type messages...
Jason
July 1, 2013 at 3:21 pm
.
July 12, 2013 at 3:46 pm
Hi Massan,
Here's a script I use to fix the orphans issue during migrations and DR recoveries. The assumption here is that the db user will have the same name as their login. It must be run in the context of the database you're attempting to fix the orphan SIDs in. sp_change_users_login will be deprecated in a future version of SQL server, but it still works in 2008 R2.
As much as I despise the use of cursors, executing dynamic sql against small numbers of rows is still a valid use.
- Rick
/* orphaned SID cleanup - Rick */
/* should be run when a database is restored to a different SQL server. */
/* identifies orphaned SIDs and then updates them one by one */
/* works when server login matches db login */
/* user failures will indicate DB users who do not exist on the new server */
/* users should be added at the server level, then re-run this script to */
/* ensure they drop off. (if they are needed on the new server) */
USE <Database_Name> /* insert the newly restored database here. Must be run in db context! */
GO
DECLARE @Temp_Users TABLE (
user_index INT IDENTITY,
UserName VARCHAR(255),
UserSID VARCHAR(255))
DECLARE @Temp_Logins TABLE (
login_name VARCHAR(255),
login_typo VARCHAR(255),
login_privilege VARCHAR(255),
mapped_login_name VARCHAR(255),
permission_path VARCHAR(MAX))
/* report of orphaned SIDs in newly restored DB */
INSERT INTO @Temp_Users (UserName,UserSID)
EXEC sp_change_users_login 'Report'
/* since attempting to sync the SIDs of users who have no logins throws a spurious error, I'm joining
a list of server logins so the process can run through automation with only valid errors */
INSERT INTO @Temp_Logins (login_name,login_typo,login_privilege,mapped_login_name,permission_path)
EXEC master..xp_logininfo
DECLARE @current_user VARCHAR(255) = NULL,
@sql_string NVARCHAR(MAX) = NULL
DECLARE cursed_cursor CURSOR FOR /* not a fan of cursors, but this is valid use */
SELECT UserName
FROM @Temp_Users
JOIN @Temp_Logins
ON UserName = login_name
ORDER BY UserName
OPEN cursed_cursor
FETCH NEXT FROM cursed_cursor INTO @current_user
WHILE @@FETCH_STATUS = 0
BEGIN
/* maps db users to server logins (syncronizes their SIDs) */
SELECT @sql_string = 'sp_change_users_login @Action = '
+ '''update_one'''
+ ',@UserNamePattern = '
+ '''' + @current_user + ''''
+ ',@LoginName = '
+ '''' + @current_user + ''''
EXEC (@sql_string)
/* repairs any db user - server login links that are broken */
SELECT @sql_string = 'sp_change_users_login @Action = '
+ '''Auto_Fix'''
+ ',@UserNamePattern = '
+ '''' + @current_user + ''''
EXEC (@sql_string)
FETCH NEXT FROM cursed_cursor INTO @current_user
END
CLOSE cursed_cursor
DEALLOCATE cursed_cursor
GO
No guarantees, but this works for me on a regular basis.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply