SQL Server Performance Forum – Threads Archive
basic dba question
I have moved the sql server file from 1 machine to another machine.After I restore it, it lost the permission setting and the user config
I remember that there is a command to recover, can someone remind me what it is? THanks
This code will help, but may not resolve all your issues, depending on your circumstances. Don’t run this code unless you thoroughly understand it and what it does.
USE database_name –Change to active database name
go DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ‘ user name being resynced’ EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName FETCH NEXT FROM orphanuser_cur INTO @UserName
END CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Nah, I dont think it is the thing I am thinking.
There is just a command or store procecdure to do that.
I just can’t remember. please help
Perhaps you can be more specific. Did you move a single database, or did you move all of the databases, including the master database? If you moved a single database, then the above script is the correct one to sync log in and database user Ids. If your log in IDs for both servers are don’t synched, you need to do this before runnning the above script. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
There is no command if you only restore database. That is why Brad ask for more information.
One thing is database users and other is SQL users. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I think that one command/stored procedure you are looking for is within Brad’s script. It is ‘sp_change_users_login’.
By the way, that’s a neat little script that I can use Brad. Every time I do a restore I use to manually go through all the logins and fix one by one with ‘sp_change_users_login’.
Now, I guess I can fix all at one shot using this script.
quote:Originally posted by bradmcgehee
This code will help, but may not resolve all your issues, depending on your circumstances. Don’t run this code unless you thoroughly understand it and what it does.
USE database_name –Change to active database name
go DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ‘ user name being resynced’ EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName FETCH NEXT FROM orphanuser_cur INTO @UserName
END CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
USE database_name –Change to active database name
go DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ‘ user name being resynced’ EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName FETCH NEXT FROM orphanuser_cur INTO @UserName
END CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
I know what you’re thinking of, but for some reason cant remember the sp, but will try. What brad posted is the standard acceptable solution for orphaned users. Its the one documented in books online and well worth using.
It was ‘sp_change_users_login’ that I was thinking of (always slips my mind), but it has limitations. what the brain cant find on a friday afternoon before a holiday….I think my brain left on holiday already
I think you are right on that one Chris (The brain left on holiday part) [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />] Because, the same sp_change_users_login was mentioned in Brad’s post and my post.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ChrisFretwell</i><br /><br />It was ‘sp_change_users_login’ that I was thinking of (always slips my mind), but it has limitations.<br /><br />what the brain cant find on a friday afternoon before a holiday….I think my brain left on holiday already<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
I use this script all the time when copying production databases to test and development databases (where the login ids already exist.) —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
,… and remember this SP_CHANGE_USERS_LOGIN can be used only for SQL Server logins; it cannot be used with Windows logins. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>