September 8, 2011 at 7:28 am
When restoring a database to a different server, do you still need to run a fixed orphaned users type script for windows logins, as I thought the SID was brought down from Active Directory, as opposed to generated by SQL for SQL Logins?
Thanks
Steve
September 8, 2011 at 7:36 am
we've seen orphaned users which are actually windows logins.
sp_help_users_login 'report' doesnt list orphaned windows logins.
you'll hv to write a script to match SIDs bettween database_principals and server_principals.
September 8, 2011 at 10:38 am
stevehindle12345 (9/8/2011)
When restoring a database to a different server, do you still need to run a fixed orphaned users type script for windows logins, as I thought the SID was brought down from Active Directory, as opposed to generated by SQL for SQL Logins?Thanks
Steve
In theory you will not need to fix orphan users for Windows Logins provided both servers are in the same domain and the AD Account has never been deleted and recreated.
Are you having a specific issue, or just wondering?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2011 at 2:15 am
More wondering really.
I agree, my understanding is the SID is brought down from AD upon creation within SQL so if it's in the same domain and the account is not one that may have been recreated between restores then the SID has to be the same.
I suppose if you really want to be sure there would never be any harm in running:
EXEC sp_change_users_login 'Auto_Fix', '<DBLogin>', NULL, '<Password>';
as this remaps both SQL AND Windows logins doesn't it?
Thanks
Steve
September 9, 2011 at 10:00 am
stevehindle12345 (9/9/2011)
More wondering really.I agree, my understanding is the SID is brought down from AD upon creation within SQL so if it's in the same domain and the account is not one that may have been recreated between restores then the SID has to be the same.
I suppose if you really want to be sure there would never be any harm in running:
EXEC sp_change_users_login 'Auto_Fix', '<DBLogin>', NULL, '<Password>';
as this remaps both SQL AND Windows logins doesn't it?
No. Direct from code in sp_change_users_login:
-- VALIDATE PARAMS --
-- Can ONLY remap SQL Users to SQL Logins! Should be no need
-- for re-mapping NT logins, and if you try, you'll mess up
-- the user status bits!
EXEC sys.sp_helptext
@objname = N'sys.sp_change_users_login' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2011 at 10:04 am
Ah, so this only remaps SQL Logins. Is there a script available for Windows logins or if a Windows login is deleted and recreated before a restore would you have to manually recreate the Windows login SQL?
September 9, 2011 at 10:15 am
stevehindle12345 (9/9/2011)
Ah, so this only remaps SQL Logins. Is there a script available for Windows logins or if a Windows login is deleted and recreated before a restore would you have to manually recreate the Windows login SQL?
I do not know of a proc that does what you're asking.
I would go so far as to say that deleting a Windows account and then creating one with the same name in between a backup and restore operation is a corner-case. Is it a common occurrence in your environment?
If you know your problem Database Users here is code to map them to the proper Server Login:
USE DatabaseName
GO
ALTER USER [orphaned_user_name]
WITH
LOGIN = [login_name],
NAME = [orphaned_user_name], -- important on some SQL builds to specify NAME explicitly otherwise [orphaned_user_name] is auto-renamed [login_name]
;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply