November 7, 2007 at 1:36 pm
I noticed when I restore a master database to a server other then the one which created the backup of master, SQL Server contains the following three local security groups that were defined on the source server. The problem is these groups are "local" and do not apply to the server where master was restored.
ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName
For example, if you have a default SQL Server instance named MARKETING_TEST the security folder will contain the following three entries.
MARKETING_TEST\SQLServer2005MSFTEUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TEST\SQLServer2005MSSQLUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TEST\SQLServer2005SQLAgentUser$MARKETING_TEST$MSSQLSERVER
If you then backup the master database on an instance named MARKETING_PROD and restore it to MARKETING_TEST, the security folder on MARKETING _TEST will now contain the following three entries.
MARKETING_PROD\SQLServer2005MSFTEUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PROD\SQLServer2005MSSQLUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PROD\SQLServer2005SQLAgentUser$MARKETING_PROD$MSSQLSERVER
These entries would be invalid because no such server exists and therefor no such local groups exists. There appears to be no Microsoft documentation explaining how to handle these groups when restoring master from one server to another. My assumption is that whenever restoring master to another server you must drop these three groups and add the correct corresponding groups along with the appropriate permissions. I don't understand why SQL Server would not rebuild this information for you during a restore.
Any explanations?
Dave
November 7, 2007 at 6:17 pm
No idea, but it makes some sense. If you restore master, it's not meant to recover on another server. If you recover somewhere else, it's expected (I'm guessing), you will have renamed this server to the same as the old server and already installed SQL.
Are you recovering for testing purposes? There are other processes that allow you to move logins instead of restoring master.
November 7, 2007 at 9:35 pm
Hi Steve,
We're recovering to build Production, QA and development environments from one master database. I know I can transfer logins, recreate backup devices, linked servers and database objects, but in 2000 it was very easy to restore master to a server with a different name. All you needed to do was place the server in single user mode, restore master, run sp_dropserver / sp_addserver and that was it.
I tried to add the three local groups to the SQL instance, but despite the local group names being different, SQL Server said the local groups already existed. This makes me believe SQL Server is looking at the local groups' SID and not the group name. Makes sense. I'm guessing during the restore the SID is updated in the system table(s), but the name apparently is not.
This sounds like a similar issue that existed in 2000, but resolved in 2005, where msdb was restored to a different server, but sysjobs.originating_server was not updated.
Thanks, Dave
November 9, 2007 at 3:30 am
I would advise against restoring master in your scenario. It is far better to transfer the logins via scripts.
The system objects in master may be different depending on te SP or HF level. If your servers ever have different fix levels you could have problems. If you restore master from a different fix level you will probably be outside of Microsoft support. There is also a risk something may not work as expected, or a future upgrade may fail.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply