May 24, 2012 at 7:54 am
hi,
so , if i restore a database from Server1 to Server 2. All the user accounts are still listed in the database on Server 2 and still have access to that database.
Is it ok to leave them as is? I belive this is called a orphan account?
Whats best practise for them? delete and re-create from the instance level?
Leave as is, it does work after all. and i guess keeps logins clean since if you do it for all db then you dont have a big list of users if you have 1000s of logins.
Thanks for any advice!
May 24, 2012 at 8:04 am
Only SQL authentication accounts become "orphaned" this is due to the SID's being different on Server1 to Server2. The exception to this is if the SQL accounts have been created on Server2 with the same SID's to prevent such "orphaning" to happen.
Generally you would use something like ALTER LOGIN or sp_change_users_login to manually update the SID's after a restore.
Best practise would be to correct only the logins which need correcting using either of the two methods above, dropping and re-creating is a lot more work than just running sp_change_users_login 'update_one','Server1User','Server2User'
May 24, 2012 at 8:21 am
using sp_help_revlogins to transfer your sql logins will recreate them with the same SID, however I have seen occasions especially when moving from different version when you still need to follow the steps identified by Anthony.
May 24, 2012 at 8:22 am
this link details how it works, useful to know if you haven't come accross it before
May 24, 2012 at 10:58 am
anthony.green (5/24/2012)
Only SQL authentication accounts become "orphaned"
Unless of course you're crossing domains 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 24, 2012 at 11:09 am
Perry Whittle (5/24/2012)
anthony.green (5/24/2012)
Only SQL authentication accounts become "orphaned"Unless of course you're crossing domains 😉
Or consider a DB user without a server login to be orphaned.
May 25, 2012 at 7:47 am
hey all,
thanks for the replies!
all the accounts are windows auth.
i admit i only do accounts via the GUI. So i have to add them to the instance to give them access to the db. I will play around with t-sql..
For SQL Users. I take it the only problem with accounts and SIDs is for things like application auto failover wont work with them having different SIDs?
Would best prac for windows users be to script straight to db like SQL users and only use the instance security for instance level groups, users...
Thanks for the help!
May 25, 2012 at 7:59 am
n00bDBA (5/25/2012)
For SQL Users. I take it the only problem with accounts and SIDs is for things like application auto failover wont work with them having different SIDs?
If you use TSQL to create them and specify the SID so it's the same on both sides that's not a problem. We do that and I never worry about lost access when moving a DB.
n00bDBA (5/25/2012)
Would best prac for windows users be to script straight to db like SQL users and only use the instance security for instance level groups, users...
I'm not quite sure what you're asking. A user (db level) doesn't work without a login (instance level).
May 25, 2012 at 8:53 am
maybe im looking wrong then.
If i look in security at instance level. It only shows the default users
If i look insecurity at db level. It shows all the users restored with the db.
These users are all windows accounts/group logins and i can connect to the db with execute as login xxx
May 26, 2012 at 3:46 am
cfradenburg (5/25/2012)
A user (db level) doesn't work without a login (instance level).
It does, but you to use EXECUTE AS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply