When working with SQL Server there are often times where you will need to script your logins over to another server such as when you have database mirroring or log shipping configured for certain databases.   This is such a common thing that Microsoft provides in-depth instructions on how to do this as well as the T-SQL code required to do it, which you can read more about here: http://support.microsoft.com/kb/918992.  The script they provide is missing a few things so I took a little time to clean up the code, revise some of it, and add in a few of the missing pieces.

The three things that are overlooked in the Microsoft article that I have included are default language and default database (being mandatory) for logins as well as the scripting of roles.  The lack of the default language is pretty minor, but it’s something that could be important to people so I am including it in my script.  The lack of default databases being an option in the Microsoft script is not a big deal because it assumes you are scripting everything, but if you are only concerned with logins that are specific to a certain database you will get errors if you haven’t created that database on the target.  The lack of server roles is another important one that I have needed in previous configurations so it is also included here.  The resulting script is pretty long so you can check out the source of the revised procedure after the jump, which includes the sp_hexadecimal script from the Microsoft article.

Additionally, something just as important as the revlogin script itself is the question of “Well now that I have this script, how do I schedule this to happen automatically for me instead of just generating more code that I have to execute on the target server?” If you are asking that question then you might want to take a look at the next block of code. In order to automate sp_help_revlogin there are a few options, but the one that I prefer over all else is using osql and a SQL Agent job with a single T-SQL step because it is very simple to implement in most server configurations. The following script is relatively straight forward in that you only have one variable, the target server, to worry about. The only pre-requisites being that xp_cmdshell is enabled and that the Windows account that the job is being executed under has sysadmin rights to both servers. This script could easily be turned into a stored procedure but for the sake of brevity I have included it in its shortest form.

Disclaimer: Use these scripts at your own risk!!

-- Declare and set the target server...
DECLARE @TargetServer SYSNAME
SET @TargetServer = N'DB1.DOMAIN.LOCAL'

-- Define a temporary file to store the script output
DECLARE @guidfile VARCHAR(160)
SET @guidfile = '%TEMP%\' + CONVERT(SYSNAME, NEWID()) + '.txt'

-- execute sp_help_revlogin_roles and save the output to the temp file
DECLARE @cmd VARCHAR(8000)
SET @cmd = 'osql -E -n -h-1 -d master -w 8000 -Q "exec sp_help_revlogin_roles" -o "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- execute the temp file on the target server
SET @cmd = 'osql -E -S ' + @TargetServer + ' -d master -w 8000 -i "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- delete the temp file
SET @cmd = 'del "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

Click here to view the code for sp_help_revlogin_roles