Migrating Databases 2005 to 2005 - RevLogin Help

Question

Post reply

Migrating Databases 2005 to 2005 - RevLogin Help

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
August 29, 2011 at 2:39 pm

#243907

We have a dying server. We created another server (virtual) and have installed SQL 2005 on it and brought it up to the same sp level as the dying production box.
Dying Server's name = SC
New VM Server name = SV
I ran 'EXEC sp_help_revlogin' against dying server SC and got this is return.
===============================================================================
-- Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login: NT AUTHORITY\SYSTEM
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login: SC\SQLServer2005MSSQLUser$SC$MSSQLSERVER
CREATE LOGIN [SC\SQLServer2005MSSQLUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login: SC\SQLServer2005SQLAgentUser$SC$MSSQLSERVER
CREATE LOGIN [SC\SQLServer2005SQLAgentUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login: SC\SQLServer2005MSFTEUser$SC$MSSQLSERVER
CREATE LOGIN [SC\SQLServer2005MSFTEUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login: NT AUTHORITY\NETWORK SERVICE
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
========================================================================================================
Now from what I read I should take the above script of all the logins to create and run these on the new server SV. However, this is what I get...
"Msg 15025, Level 16, State 2, Line 2
The server principal 'BUILTIN\Administrators' already exists.
Msg 15025, Level 16, State 2, Line 5
The server principal 'NT AUTHORITY\SYSTEM' already exists.
Msg 15401, Level 16, State 1, Line 8
Windows NT user or group 'SC\SQLServer2005MSSQLUser$SC$MSSQLSERVER' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 11
Windows NT user or group 'SC\SQLServer2005SQLAgentUser$SC$MSSQLSERVER' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 14
Windows NT user or group 'SC\SQLServer2005MSFTEUser$SC$MSSQLSERVER' not found. Check the name again."
Should I be creating these logins after I migrate these users databases over to this new server? Is that the reason for the error message? Also, why am I not seeing the SID, and Hex password value in the result?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

naresh.talla SSCommitted Points: 1929 More actions · Answer 1

don't create built in\administrator and NT\authority..

when ever your installing sql server that logins are created..

matt.bowler SSCrazy Points: 2677 More actions · Answer 2

You are not seeing a SID or a password because all of those logins are from Windows AD, rather than SQL Server logins. As long as the new server is in the same domain you should not have any problems.

naresh.talla SSCommitted Points: 1929 More actions · Answer 3

only user login creates sid and password ..

not built in and nt\authority

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 4

Did you run the script against the master database rather than running it against the required database by any chance? Try running again against the required database.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

the documentation for that revlogin script tells you to carefully examine the output before executing it. There are certain logins that you do not want to re create on the new server, the objects listed above being the ones in particular

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 6

I ran RevLogin against all the user databases and it didn't show anything different than before.

What kind of logins are these?

-- Login: SC\SQLServer2005MSSQLUser$SC$MSSQLSERVER

CREATE LOGIN [SC\SQLServer2005MSSQLUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: SC\SQLServer2005SQLAgentUser$SC$MSSQLSERVER

CREATE LOGIN [SC\SQLServer2005SQLAgentUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: SC\SQLServer2005MSFTEUser$SC$MSSQLSERVER

CREATE LOGIN [SC\SQLServer2005MSFTEUser$SC$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

When I grab all the users from sys.sysusers I get one called RXEXecRole and ##MS_AgentsigningCertificate## but they are not showing up when i run RevLogin.

matt.bowler SSCrazy Points: 2677 More actions · Answer 7

Those logins look like the domain accounts setup for running the various SQL Services (engine, agent & full text).

Run sp_help_rev_login against the master database as this is dealing with logins. These logins will then need to be correctly mapped to users in the various databases. sp_change_users_login 'report' will help with this.

http://msdn.microsoft.com/en-us/library/ms174378.aspx

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

matt.bowler (8/30/2011)
Those logins look like the domain accounts setup for running the various SQL Services (engine, agent & full text).

they're not acounts they're local groups and hence invalid when applied to another server

Vertigo

the documentation clearly states to execute revlogin against the master database

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉