Transferring SQL Server Logins

Well, this is going to be a rather short one, but as I frequently come across this issue I'd like to put it here.

If you are using a second (or third, etc.) SQL Server instance, e.g. as Standby-System, Mirror, Test-System or whatsoever you encounter the problem of "How to automatically transfer the SQL Server Logins to the remote Server?"

While the Users (aka "database principals") are actually copied with the Database-Backup, the Logins (aka "server principals") are on SQL Instance level (thus, stored in "master" db) and are not part of such a backup.

I consider it public knowledge – more or less – that Microsoft provides a script/feature to actually accomplish this; see this KB articles about the details:

Bottom line is that herewith a Stored Procedure "sp_help_revlogin" is created within the "master" db. This SP generates a TSQL script to create the logins.

The cool thing is, that this script features the correct SID (Windows Logins) and passwords (encrypted; Database Logins). Thus, all you need to do is to create the script on the main server and execute it on the remote server! No problem to do that manually in Management Studio, but how to perform this automatically?

There are two options:

  1. The "standard" procedure would be to use the corresponding SSIS task; which has nothing to do with "sp_help_revlogin", which frequently fails and generally sucks and will be ignored further on
  2. Using simple command line feature "SQLCMD"

See "Books Online" about details; here the basic syntax:

sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format] ]
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]
[ -? show syntax summary ]

So "SQLCMD" could be used to execute the SP "sp_help_revlogin" on the "Principal", saving the result – the script which finally created the logins – into file; for example:

sqlcmd -E -S "SQL_Principal" -d master -Q "exec sp_help_revlogin" -o "C:\temp\Logins.sql"

Using Windows Authentication [-E] and a Server [-S] named "SQL_Principal", executing the Query [-Q] "exec sp_help_revlogin", saving the output [-o] to file "C:\temp\Logins.sql".

Then a second command could execute the output script on the "Mirror":

sqlcmd -E -S "SQL_Mirror" -d master -i "C:\temp\Logins.sql" -o "C:\temp\Login_Errors.txt"

Using Windows Authentication [-E] and a Server [-S] named "SQL_Mirror", executing the input script [-i] "C:\temp\Logins.sql", saving the output [-o] to file "C:\temp\Login_Errors.sql".

These two commands could be put into a small batch/cmd-file and executed periodically, e.g. via Windows Task Planner.

Yep, the "goode olde command shell" still rocks!

Posted: 2010-11-10 9:25 by Jörg A. Stryk | with 2 comment(s) |
Bookmark and Share

Comments

# re: Transferring SQL Server Logins

Hi Jörg,

why not using SQL Server Agent and Linked Server or Openquery with sp_executesql/exec?

best regards

Tobias

Wednesday, November 10, 2010 11:30 AM by Tobias

# re: Transferring SQL Server Logins

Sure, there are many ways to actually execute the SP "sp_help_revlogin"; the problem is basically how to execute the OUTPUT/SCRIPT of the SP  ...

Do you think this could be accomplished with OPENQUERY? Could you give an example?

Wednesday, November 10, 2010 12:50 PM by Jörg A. Stryk