January 28, 2011 at 9:31 am
Here is my scenario:
1) Source database is on a 2008 production instance
2) A nightly copy is made of this database using snap manager
3) The copied database is attached to another 2008 instance
4) Process is managed via SQL Agent job
Two questions:
1) What is the best way to drop the logins on the newly attached database that are no longer needed using T-SQL?
2) What is the best way to re-sync the logins that do exist on the 2nd server with the logins in the newly attached database using T-SQL?
Thanks
January 28, 2011 at 10:06 am
Create a script that will step through the existing user accounts (sys.sysusers) and delete those that you don't want and that don't match up to sys.syslogins. Then have a script that will re-create the ones you do want.
Depending on the security work that's been done, user accounts with complex setups (different access levels to different objects, etc.), that create script might be complex.
Another option is to re-create the logins on the second server to give them the SIDs from the primary server, and then the user accounts will be in-sync between the two servers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 11:13 am
Use:
exec sp_change_users_login @action= 'AUTO_FIX', @usernamepattern = 'username';
A.J.
DBA with an attitude
January 28, 2011 at 1:26 pm
Answer to question 2) - Use the sp_help_revlogin script on the first server to script out the logins. Use this script on the second server to create the logins in the first place and you will never have any user synching issues to resolve.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply