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.