Often enough we encounter a situation where we need security to be migrated. Microsoft hasn’t made this task as easy as moving other things such as SQL Agent jobs since restoring the master database to another server is tricky at best but they do provide us some tools.
A couple of common reasons that you’d want to move logins is if you are standing up a test server and want the servers to match identically or you are standing up a disaster recovery server and might need to re-sync logins as changes are made to the primary.
Preferred Method: T-SQL
I personally prefer using T-SQL over SSIS whenever the situation leaves to the two methods on an even playing field so I use the Microsoft provided stored procedures even though I will go over the SSIS method below.
sp_help_revlogin is a stored procedure which will satisfy two main feats while printing out all of the necessary create statements for all of your instance logins. The first is that it returns as part of the create statements all of the SIDs for your SQL accounts. Domain accounts don’t require this step but when creating a new SQL account and then restoring a legacy database to that instance you often are left with orphaned users because the database level user exists and so does the login but their SIDs don’t match. The second feat is that it returns the SQL account passwords in hex format so that you aren’t exposing the passwords to anyone. But DBAs who have no need to know those passwords can still create the new user.
As found in KB918992 and KB246133 there are two stored procedures that Microsoft provides but are not installed implicitly. The first is sp_hexadecimal which is a function used to pull SQL account passwords in hex so that they aren’t directly exposed to the person executing the stored procedure sp_help_revlogin. sp_help_revlogin is the primary procedure that you execute and it takes a single optional parameter @login_name (sysname). @login_name is NULL by default and omitting it will return all logins for the instance. If you pass in the login name then you will receive the statement for just that user.
NOTE: The KB articles where you get the stored procedure only list versions 7.0 through 2005 as applicable but I have tested this on 2008 and 2008 R2 and it works fine.
EXEC dbo.sp_help_revlogin --OR EXEC dbo.sp_help_revlogin @login_name = 'BlogTester' -- sysname
/* sp_help_revlogin script ** Generated Aug 20 2012 8:24AM on V-DEV-DB-011\vb18 */ -- Login: BlogTester CREATE LOGIN [BlogTester] WITH PASSWORD = 0x01000D1F43BB2A1F306EC90F5352291E8DD273549DB4AF950845 HASHED, SID = 0xD831296B0274D448A5748A52B8C796EA, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Only members of the fixed server role ‘sysadmin’ can access the system table master..sysxlogins which is required to execute sp_help_revlogin therefore you will need to be a sysadmin in order to run this procedure.
SSIS Method: Transfer Logins Task
BIDS provides us with the Transfer Logins Task. This task will transfer logins from one SQL Server source to one SQL Server destination. It can transfer all logins, a single login, or specific logins selected as a collection. It also can be configured to overwrite, skip, or throw an error when it reaches a login that already exists.
See the technet post for more details.
This method also requires you to be a sysadmin of the source server, in addition, it requires sysadmin to the destination server.
Beware of this method
The SSIS Method does not pull over existing passwords like the T-SQL method does. Instead it will assign random passwords to each of the SQL accounts that it creates. You will then after to go through and manually (or via script) update every password for the accounts that were created.
Important Planning Note
The create statements with both methods will include the default database for the login. If this is not set to master or a database that already exists on the destination then there will be errors and at least the create statement in question will fail. I find it easiest to handle security after I’ve done most other tasks involved in my migration or instance sync.