SQL Server Login Transfer Scripts

--The following code script out the sp_addlogin calls for the SQL Server logins:

SET NOCOUNT ON

SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''' ,', @defdb = ''' + dbname + '''' ,', @deflanguage = ''' + language + '''' ,', @encryptopt = ''skip_encryption''' ,', @passwd =' , cast(password AS varbinary(256)) ,', @sid =' , sid FROM syslogins WHERE name NOT IN ('sa') AND isntname = 0

/*
Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don't lose characters.
*/


--To script the NT logins:

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + '''' ,' EXEC sp_defaultdb @loginame = ''' + loginname + '''' ,', @defdb = ''' + dbname + '''' FROM syslogins WHERE loginname NOT IN ('BUILTIN\Administrators') AND isntname = 1

/*
Save the output as a file and execute that file in the destination server. You would get an error message if a login already exists. If you don't want to get the error messages, script with IF NOT EXISTS and a check against the login name column in the destination server for each call to sp_addlogin and sp_grantlogin.
*/