Subject: to transfer and synchronize SQL server logins, SIDs, SQL Login Passwords and permissions from one server instance to another by using SSIS package
This technical applies to: SQL server migration, Primary server and second server login synchronization including SIDs, Passwords and permissions transfer. SQL server 2008 and later is applicable.
1. Real world Issues
It is too often when a principal server failover to a mirror server, the SQL server logins are found is not totally in sync with the principal server. This is same to the log-shipping servers, replicated sites and other DR scenarios like Backup/restore databases from one server to another.
As I mentioned in my last post: A simple Way to Migrate SQL Logins from One Instance to Another, it is not convenient to transfer server logins from one server to another.
2. Login Synchronization Consideration
It will be nice if we can have an automated process or script to transfer logins from one server to another including SIDs, passwords and permissions.
Typically, to transfer sql server logins from one server to another automatically, the following needs to be considered:
A) Login SIDs (either windows logins or SQL logins) in Destination server but not in source, these logins in destination server need to be dropped
B) SQL logins have different passwords between Source server and destination server, the SQL Logins in destination server needs to be altered
C) Login names in Destination server not in source server need to be altered with same name as in source
D) Logins in Source server but not in destination server, these logins in source server need to be synchronized from source to destination server.
E) Logins with different Is_Disabled value between source and destination. The destination logins need to be altered.
F) Logins’ server role assignments need to be synchronized from source to destination.
G) Login permissions need to be synchronized from source to destination.
H) There are others need to be considered like default language etc. but the above mentioned are most important ones.
I have transformed these requirements into the SSIS tasks as below:
A) Drop logins in destination where SIDs are different
B) Drop logins in destination where passwords are different (Drop is simpler than alter.)
C) Drop logins in destination where names are different (Drop is simpler than alter)
D) Check and Sync Logins
E) Alter logins in destination where Is_Disabled are different
F) Check and Sync role members
G) Check and sync permissions
The SSIS package lay out as below:
3. SSIS Package design
The package contains 7 containers and each container has contained 3 tasks: a Data flow task, A ForEach Loop task, and a Execute SQL script task.
The general idea is to use the data flow task to generate the SQL script and store the query text in a recordset destination which assigns to an object variable: Query_Dataset; the ForEach task use an ADO enumerator to loop through the Query script records in the Query_Dataset variable and assign it to a string variable:SQL_Query; the Execute SQl script task runs the string query variable to apply the change.
The variables have been setup as below:
A) Drop logins in destination where SIDs are different Task Design
The data flow task: Get login with SIDs diff task has the below design:
This is use merge join to find out any SIDs in Destination but not in Source. The conditional split is to split out any NULL SID value from the Login source in the merge Join component.
The derived column component is used to generate the DDL query like: “Drop Login MyLoginName;”
The query is stored in the Query_dataset where Query_Dataset variable is configured.
B) Drop logins in destination where passwords are different task design
The data flow task: Get Login with Password diff task design is depicted as below:
C) Drop logins in destination where names are different Task Design
A Login with same SID in Source and destination server could have different name if Login name in source server is altered. Therefore, these logins need to be synced to destination. To simplify the process, I simply drop those logins in destination and reimport from the source again later.
The data flow task: Get login with Name diff is depicted as below:
D) Check and Sync Logins task design
In this task I will use the SQL script I have created in my last post to generate the Create Login DDL queries.
The query will script out the source login SIDs and also passwords if they are SQL logins.
The data flow task: get server login diff task is depicted as below:
The script will script out both windows logins and SQL logins but will leave out the R = Server role, C = Login mapped to a certificate, and K = Login mapped to an asymmetric key. As in SQL server 2008, server role is fixed. The C and K are more complicated and I just want to keep things simple here.
The script in Login source is as below:
Select Upper(A.name) As Name,
Case When A.type IN ('G', 'U')
Then
'Create Login ' + QUOTENAME(A.name)
+ ' From Windows With DEFAULT_DATABASE=[Master]' --+ quotename(A.default_database_Name)
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
When A.type IN ('S')
Then
'Create Login ' + QUOTENAME(A.name)
+ ' With Password=' + CONVERT(varchar(max), B.password_hash, 1) + ' hashed'
+ ', DEFAULT_DATABASE=[Master]' --+ quotename(B.default_database_Name)
+ ', DEFAULT_LANGUAGE=' + quotename(B.default_language_Name)
+ ', CHECK_POLICY=' + Case B.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', CHECK_EXPIRATION=' + Case B.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1)
End As SRVLogin
From sys.server_principals A
Left Outer Join
sys.sql_logins B
On A.name = B.Name
Where A.type Not In ('R', 'C', 'K')
And A.name Not like '##%##'
And A.sid != 0x01
E) Alter logins in destination where Is_Disabled are different
This task is used to keep the is_disabled value synced between source and destination. The data flow task: get login with IsDisable diff is depicted as below:
F) Check and Sync role members task design
This task is used to sync the server role members between source and destination. I get both role name and its members from source and destination servers. A full join component is used to find out which role members for a server role is in source but not in destination and vice versa. For those logins in a server role in source but not in destination, then an sp_addsrvrolemember script will be created. In contrast, for those logins in a server role in destination but not in source, an sp_dropsrvrolemember script will be created.
Both scripts will be combined and save into the Query_Dataset variable. The data flow task: Check role Member task is depicted as below:
G) Check and sync permissions task design
This task is used to sync the login permissions from source to destination. This query is used to script out the login permissions:
Select distinct
A.name
, B.Permission_Name
, B.state
, B.state_desc
From sys.server_principals A
Inner Join
sys.server_permissions B
On
A.principal_id = B.grantee_principal_id
Where A.type Not In ('R', 'C', 'K')
And A.name Not like '##%##'
And A.sid != 0x01
A full join is used to find out which permissions are applied in source for a login but not in destination and vice versa.
For those permissions are applied in source for a login but not in destination then those logins will go to the Permission_Applied route. And the below expression is used to create the permission grant script:
(DT_WSTR,2000)([state_desc source] + " " + [Permission_Name source] + " TO [" + [name source] + ([state source] == "W" ? "] WITH GRANT OPTION;" : "];"))
For those permissions are applied in destination for a login but not in source then those logins will go to the Permission_Removed route. And the below expression is used to create the permission grant script:
(DT_WSTR,2000)("REVOKE " + [Permission_Name dest] + " TO [" + [name dest] + "];")
The data flow task: Check Permissions task is depicted as below:
4. SSIS Package Automation
An SQL agent Integration service job can be used to automate the Login synchronization process. The procedure is to create a job to use the package.
For example, a job name can be: Apply Login Changes from Source to Destination.
And Remember to set your source and destination server variable value to your server names like the below:
Once the job is set up you can schedule the job to run automatically in your preferred time intervals.
You can also create a server level DDL trigger to automatically run the job whenever an DDL command is performed on logins. An example of the trigger like:
Create Trigger trigger_ApplyLoginsToDestination
On All Server
After Create_Login, Alter_Login, Drop_Login
As
Exec msdb..sp_start_job 'Apply Login Changes from Source to Destination' --change to your own job name
Print 'You have synced your login changes from source to destination server.'
You can download this package from the attachment in this post.
Thanks for reading.
SQLServer_Login_Sync_Blog.zip (47.20 kb)