December 2, 2011 at 1:37 pm
I've been tasked with documenting a number of databases that need to backed up and restored on a new server. My questions is if I document the Server Logins and the databases they are mapped to will that be sufficient enough info as far as the users and logins are concerned?
Once a server login is created and mapped to a database does it automatically become a user for that database or des a user need to be added as well under the database/users folder?
December 2, 2011 at 1:46 pm
If you are restoring a database the users wil be restored with the database
I would use sp_help_revlogin to create the SQL to create the logins on the new server, this will also document them.
If you then run the script produced by sp_help_revlogin on the new server the logins and database users will automatically tie up.
---------------------------------------------------------------------
December 2, 2011 at 2:22 pm
I went an extra step and wrote a script to bring the logins accross and maintain their internal SID values, tis meant when the database was restored I didn't have to "fix" them.
This is a chopped down version that doesn't include any private information:
SELECT Cmd = 'CREATE LOGIN ['
+ name
+ '] WITH PASSWORD = '
+ CONVERT( varchar(255), password_hash, 1 )
+ ' HASHED, SID = '
+ CONVERT( varchar(255), sid, 1 )
+ ', DEFAULT_DATABASE = ['
+ default_database_name
+ '], CHECK_EXPIRATION = '
+ CASE is_expiration_checked WHEN 1 THEN 'ON, ' ELSE 'OFF, ' END
+ 'CHECK_POLICY = '
+ CASE is_policy_checked WHEN 1 THEN 'ON;' ELSE 'OFF;' END
FROM sys.sql_logins
WHERE type = 'S'
AND name NOT IN ( 'sa', '##MS_PolicyTsqlExecutionLogin##' )
AND is_disabled = 0
UNION ALL
SELECT Cmd = 'CREATE LOGIN ['
+ name
+ '] FROM WINDOWS WITH DEFAULT_DATABASE = ['
+ default_database_name
+ ']; '
FROM sys.server_principals
WHERE type IN ( 'U', 'G' )
AND name NOT IN ( 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT' )
AND is_disabled = 0
GO
It does SQL Logins then Non-SQL Logins. We don't have to worry about the SIDs for domain accounts since the value stored is always the same no matter where in the domain you are..
CEWII
December 2, 2011 at 2:48 pm
Elliot, sp_help_revlogin maintains sids and passwords
---------------------------------------------------------------------
December 2, 2011 at 3:57 pm
My understanding was that it fixed the references internally. My process didn't require such adjustments, the SIDs were the same on both servers therefore when the restore occured I didn't have to do anything to line them back up. It also carried the passwords over without having to know that they were.
This worked GREAT for me..
YMMV..
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply