http://www.sqlservercentral.com/blogs/brian_kelley/2006/05/24/disaster-recovery-and-sql-server-part-ii/ Printed 2017/06/24 10:01AM
Disaster Recovery and SQL Server, Part II
2006/05/24In 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:
SELECT 'ALTER LOGIN [' + name + '] WITH DEFAULT_LANGUAGE = ['
+ default_language_name + ']'
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.