Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions

April 5, 2012 at 3:54 AMSteven Wang

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)

Comments (25) -

Hi,

In my environment the instance name are different on Principal and Mirror Server. This is causing the package to fail as the corresponding NT service user names are different.

for ex:
Principal : NT SERVICE\MSSQL$UATCSDB01_DB
Secondary : NT SERVICE\MSSQL$UATCSDB02_DB

Please confirm on any alternative for this.

Thanks in advance.

Regards,
Animesh

Reply

Hi Animesh,

For your situation, you can modify the connection manager: MasterDB_Destination and MasterDB_Source not to use the expression in the package. Instead, you can change the connection string for both to sue your username and password.

I believe this will work.

With regards,


Steven

Reply

can you explain more on this what you mean by expression &  string in your statement

Reply

could you quote exactly which paragraph you were referring to? I was not able to exactly catch what you want.

thanks

Reply

linux vps
United States linux vps says:

Good day. Extremely nice blog!! Man... Excellent ... Superb ... I will bookmark your website and take the feeds additionally...I'm satisfied to find numerous useful information here in the article. Thank you for sharing...
<a href="http://www.alphavbox.com">linux vps</a>

Reply

Christophe
Canada Christophe says:

Very good article and blog... We have the same kind of problem to synchronize linked servers... Is there a way to get SQL logins passwords stored in linked servers definitions?

Thanks!

Reply

best virtual server
United States best virtual server says:

Useful information shared. I am very happy to read this article..Thanks for giving us nice info. Fantastic walk-through. I appreciate this post.

Reply

I am getting following error while trying to add into a project. Is there any idea what I am missing?

********

===================================

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
(Microsoft Visual Studio)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.<>c__DisplayClass4.<LoadPackageFromFile>b__3(String password, IDTSEvents events)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageWithPassword(PackageLoader loader, IWin32Window dialogParent, String packageName, Boolean askOptionalPassword, String& packagePassword, DtsProtectionEventListener errorListener)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageFromFile(String file, IWin32Window dialogParent, Boolean askOptionalPassword, PackageUpgradeOptions packageUpgradeOptions, String& packagePassword, DtsProtectionEventListener errorListener)
   at Microsoft.DataTransformationServices.Controls.PackageLocationControl.LoadPackage(DtsProtectionEventListener errorListener, PackageUpgradeOptions packageUpgradeOptions, String& packagePassword)
   at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackage(AddExistinngPackageEventListener eventListener)
   at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackageForm_FormClosing(Object sender, FormClosingEventArgs e)

===================================

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.


------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(String FileName, Boolean loadNeutral, IDTSEvents100 pEvents)
   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)

Reply

Hi Siva,

I suspect that you were trying to load the SSIS package under SSIS 2005 version. I just to clarify that this is only working with SQL server 2008 or later.

Thanks.



Steven

Reply

linux virtual server hosting
India linux virtual server hosting says:

This is nice post which I was awaiting for such an article and I have gained some useful information from this site. Thanks for sharing this information.
feel free to visit us  http://www.alphavbox.com/

Reply

Snorri Kristjansson
Iceland Snorri Kristjansson says:

Thank you very much for sharing this. I had some problems editing the package in Visual Studio 2010. I could not change the source and destination servers names no matter what I tried. Solved that by creating new connections for source and destination servers in connection manager and replacing the connection used everywhere in the package. This is now working fine in my environment. Great solution, thanks again Smile

Reply

UltrasoundTech
United States UltrasoundTech says:

Just like the several causes described above you have many choices to make international currency exchange. <a href="theultrasoundtechnician.com/.../">What Kind Of Degree Do I Need To Be An Ultrasound Technician</a>. The recipient can use the identical transfer code to withdraw the income. You need to have not fear utilizing a funds transmitter for moving cash just as you must not worry working with a bank.

Reply

How do I edit the data sources in the download package? After you change and save everything back to the default value of Source and Destination ...

Regards
Marek

Reply

Ali Khawaja
Saudi Arabia Ali Khawaja says:

Hi Stephen - great great work.  really nice.  excellent work.  

i wanted to get your opinion whether this will be appropriate solution for alwayson enviornments as well as we need to sync users over there as well.

Regards
Ali

Reply

Thank you very much for sharing, Stephen.  It is one solid solution to tackle the login sync issue between SQL instances.  I have tried the solution and it works great in SQL 2014 as well.

Reply

steven wang
Australia steven wang says:

Thanks Mike for your comments.

cheers.





steven

Reply

Robert Miller
United States Robert Miller says:

Kudos Steven, excellent work from START to FINISH! I used to transfer 354 logins, SIDs, etc...nicely done!

Reply

Peter Willis
United Kingdom Peter Willis says:

I'm new to SSIS and I'd like to use this regularly to keep things in sync but how do I get the user deleted from the Destination server if they've been correctly deleted from the Source? The script fails until the account is recreated on the Source.

Reply

rochie mireasa
United States rochie mireasa says:

{I have been surfing online more than 2 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my view, if all web owners and bloggers made good content as you did, the net will be much more useful than ever before.|I couldn't refrain from commenting. Perfectly written!|I will right away grasp your rss feed as I can not find your email subscription link or e-newsletter service. Do you have any? Please allow me recognise so that I could subscribe. Thanks.|It's perfect time to make some plans for the future and it is time to be happy. I have read this post and if I could I desire to suggest you few interesting things or tips. Maybe you could write next articles referring to this article. I desire to read more things about it!|It is appropriate time to make a few plans for the longer term and it's time to be happy. I've read this post and if I could I desire to counsel you some fascinating issues or advice. Perhaps you could write subsequent articles regarding this article. I wish to read more issues about it!|I have been browsing online greater than three hours nowadays, but I never found any interesting article like yours. It's lovely value enough for me. Personally, if all website owners and bloggers made just right content as you did, the web might be a lot more useful than ever before.|Ahaa, its nice conversation concerning this post at this place at this weblog, I have read all that, so now me also commenting at this place.|I am sure this paragraph has touched all the internet users, its really really fastidious article on building up new blog.|Wow, this post is fastidious, my sister is analyzing these things, thus I am going to let know her.|bookmarked!!, I really like your web site!|Way cool! Some extremely valid points! I appreciate you writing this write-up plus the rest of the site is very good.|Hi, I do believe this is an excellent blog. I stumbledupon it ;) I am going to revisit yet again since I saved as a favorite it. Money and freedom is the greatest way to change, may you be rich and continue to help others.|Woah! I'm really digging the template/theme of this website. It's simple, yet effective. A lot of times it's hard to get that "perfect balance" between superb usability and appearance. I must say you have done a great job with this. Also, the blog loads very fast for me on Opera. Exceptional Blog!|These are in fact great ideas in on the topic of blogging. You have touched some nice points here. Any way keep up wrinting.|Everyone loves what you guys tend to be up too. This kind of clever work and exposure! Keep up the excellent works guys I've incorporated you guys to my personal blogroll.|Hello! Someone in my Facebook group shared this website with us so I came to check it out. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Terrific blog and outstanding style and design.|I like what you guys are up too. This type of clever work and coverage! Keep up the great works guys I've included you guys to  blogroll.|Hey there would you mind stating which blog platform you're using? I'm going to start my own blog soon but I'm having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I'm looking for something completely unique.                  P.S My apologies for being off-topic but I had to ask!|Hey there would you mind letting me know which webhost you're working with? I've loaded your blog in 3 completely different web browsers and I must say this blog loads a lot faster then most. Can you recommend a good web hosting provider at a fair price? Kudos, I appreciate it!|I really like it when folks come together and share thoughts. Great site, stick with it!|Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! By the way, how can we communicate?|Howdy just wanted to give you a quick heads up. The words in your article seem to be running off the screen in Ie. I'm not sure if this is a formatting issue or something to do with web browser compatibility but I thought I'd post to let you know. The design look great though! Hope you get the issue fixed soon. Cheers|This is a topic that's near to my heart... Many thanks! Exactly where are your contact details though?|It's very straightforward to find out any matter on net as compared to textbooks, as I found this post at this website.|Does your blog have a contact page? I'm having problems locating it but, I'd like to send you an e-mail. I've got some ideas for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it develop over time.|Hi! I've been reading your site for a while now and finally got the bravery to go ahead and give you a shout out from  Porter Tx! Just wanted to mention keep up the great work!|Greetings from Carolina! I'm bored at work so I decided to check out your blog on my iphone during lunch break. I enjoy the knowledge you present here and can't wait to take a look when I get home. I'm surprised at how quick your blog loaded on my phone .. I'm not even using WIFI, just 3G .. Anyhow, great blog!|Its such as you learn my mind! You appear to know a lot about this, like you wrote the e book in it or something. I believe that you simply can do with some percent to pressure the message home a little bit, but other than that, that is excellent blog. A great read. I will definitely be back.|I visited many web pages but the audio quality for audio songs current at this web page is genuinely excellent.|Hello, i read your blog occasionally and i own a similar one and i was just curious if you get a lot of spam responses? If so how do you protect against it, any plugin or anything you can recommend? I get so much lately it's driving me mad so any assistance is very much appreciated.|Greetings! Very helpful advice in this particular article! It is the little changes that will make the most important changes. Thanks a lot for sharing!|I truly love your website.. Excellent colors & theme. Did you build this website yourself? Please reply back as I'm hoping to create my own personal blog and would love to find out where you got this from or exactly what the theme is named. Cheers!|Howdy! This article couldn't be written any better! Going through this article reminds me of my previous roommate! He constantly kept talking about this. I am going to send this article to him. Fairly certain he's going to have a great read. I appreciate you for sharing!|Amazing! This blog looks just like my old one! It's on a totally different subject but it has pretty much the same layout and design. Superb choice of colors!|There's certainly a great deal to know about this issue. I really like all the points you made.|You've made some really good points there. I looked on the internet to learn more about the issue and found most people will go along with your views on this site.|Hi there, I log on to your blog regularly. Your story-telling style is awesome, keep up the good work!|I just couldn't depart your website prior to suggesting that I actually enjoyed the standard information an individual supply on your guests? Is going to be back regularly to investigate cross-check new posts|I want to to thank you for this great read!! I absolutely loved every bit of it. I have you book-marked to look at new stuff you post�|Hello, just wanted to tell you, I loved this article. It was practical. Keep on posting!|Hello, I enjoy reading through your article post. I wanted to write a little comment to support you.|I constantly spent my half an hour to read this blog's articles or reviews every day along with a mug of coffee.|I for all time emailed this weblog post page to all my friends, as if like to read it next my links will too.|My developer is trying to convince me to move to .net from PHP. I have always disliked the idea because of the costs. But he's tryiong none the less. I've been using Movable-type on several websites for about a year and am concerned about switching to another platform. I have heard fantastic things about blogengine.net. Is there a way I can import all my wordpress content into it? Any help would be greatly appreciated!|Good day! I could have sworn I've been to your blog before but after going through a few of the posts I realized it's new to me. Anyhow, I'm definitely delighted I discovered it and I'll be book-marking it and checking back often!|Wonderful article! That is the type of information that should be shared across the internet. Shame on the search engines for no longer positioning this put up upper! Come on over and consult with my site . Thanks =)|Heya i'm for the first time here. I found this board and I find It really useful & it helped me out much. I hope to give something back and help others like you helped me.|Greetings, I do believe your blog might be having web browser compatibility problems. Whenever I take a look at your site in Safari, it looks fine however when opening in Internet Explorer, it has some overlapping issues. I just wanted to provide you with a quick heads up! Other than that, excellent blog!|A person essentially assist to make severely posts I'd state. This is the first time I frequented your website page and to this point? I amazed with the analysis you made to create this actual publish extraordinary. Great task!|Heya i am for the first time here. I found this board and I to find It really helpful & it helped me out much. I'm hoping to provide one thing back and aid others such as you aided me.|Hi! I simply would like to offer you a huge thumbs up for your great information you have here on this post. I am returning to your blog for more soon.|I every time used to read paragraph in news papers but now as I am a user of net thus from now I am using net for articles or reviews, thanks to web.|Your means of explaining everything in this post is in fact fastidious, every one can easily know it, Thanks a lot.|Hi there, I found your blog by way of Google even as searching for a similar matter, your web site got here up, it appears to be like great. I have bookmarked it in my google bookmarks.

Reply

cancer awareness ribbons
United States cancer awareness ribbons says:

Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

Reply

Soledad Dials
United States Soledad Dials says:

I intended to post you the little bit of note to help say thanks a lot the moment again with the gorgeous views you've discussed at this time. It is really unbelievably generous with people like you to give publicly precisely what a few people would've offered for sale for an e book to get some cash on their own, certainly now that you might well have tried it in the event you considered necessary. Those points also served like a good way to be aware that many people have similar keenness really like mine to grasp a whole lot more on the topic of this issue. I am certain there are many more pleasant moments up front for many who read your site.

Reply

Britney Natali
United States Britney Natali says:

I wanted to post you one tiny remark so as to thank you very much yet again on your extraordinary basics you have shared at this time. This has been so strangely open-handed with you in giving easily what many of us could have marketed as an electronic book to make some cash on their own, specifically since you might well have done it in case you desired. Those secrets in addition acted like the great way to be certain that other people online have the identical desire really like mine to know the truth much more on the topic of this matter. I believe there are some more pleasant instances ahead for people who examine your website.

Reply

zaborilenta
United States zaborilenta says:

Excellent post however I was wanting to know if you could write a litte more on this subject? I'd be very grateful if you could elaborate a little bit further. Kudos!

Reply

furtdso linopv
United States furtdso linopv says:

I would like to thnkx for the efforts you have put in writing this blog. I am hoping the same high-grade blog post from you in the upcoming as well. In fact your creative writing abilities has inspired me to get my own blog now. Really the blogging is spreading its wings quickly. Your write up is a good example of it.

Reply

http://pittcenterforpeace.org
United States http://pittcenterforpeace.org says:

Ou avez-vous d�j� assist� � une s�ance?

Reply

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading