Printed 2016/09/29 10:00PM

Disaster Recovery and SQL Server, Part II


In a previous post, Disaster Recovery and SQL Server, Part I, I pointed to the Microsoft Knowledge Base (KB) article which contains stored procedures that generate scripts to recreate logins and passwords. This sort of thing is extremely useful for disaster recovery, especially when we have to consolidate databases and logins onto fewer servers than we have in our normal operating locations. Microsoft has published a new KB article for doing the same thing in SQL Server 2005, with an update to sp_help_revlogin. You can find that KB article here:

How to transfer the logins and the passwords between instances of SQL Server 2005

If you're seeing the article and it says last updated May 24, 2006 (Revision 2.0), there is a small bug in the script for sp_help_revlogin. A comma is left out between the SID and the DEFAULT_DATABASE parameter when dealing with a SQL Server login. The original text:

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ' DEFAULT_DATABASE = [' + @defaultdb + ']'

and what it should be (comma highlighted in red):

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
The SQL Server 2005 version of sp_help_revlogin does handle checking the local security policy for passwords, but it again leaves out the default language. Therefore, if you need to specify languages other than the default for your SQL Server setup, make sure to extract these with another script, something like:

  + default_language_name + ']'
FROM sys.server_principals
WHERE name <> 'sa'
  AND type IN ('G', 'S', 'U') 
  AND default_language_name IS NOT NULL

This script only pulls logins where the default language is set. The type specifies only Windows groups (type 'G'), Windows users (type 'U'), and SQL Server users (type 'S'). If you try to ALTER the LOGIN on another server principal, such as the certificate mapped logins (type 'C'), you'll get an error, hence the reason for the type specification in the predicate.

Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.