Steven Wang - 汪上洲

Steven is a data scientist and a Microsoft Certified Solution Expert on Data Platform, MCSA on SQL server 2008/12 and MCITP on BI Developer, Database Developer and DBA.

He is very passionate about data insights, machine learning and MS SQL server technology and business intelligence and has designed and implemented a number of large scale enterprise BI solutions in a variety of industries.

Steven is an active SQL server community participant. He answers questions at MSDN SQL server forum and speaks at events like TechEd, CodeCamp, SQL User Group etc.

Steven is currently head of data science at QuintilesIMS at Sydney Office.

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.

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.

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:

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:

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:

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 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)

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.

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/

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

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.

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.

{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.

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.

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.

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.

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.

This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want�HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

Hey! I know this is kinda off topic but I was wondering which blog platform are you using for this site? I'm getting tired of Wordpress because I've had problems with hackers and I'm looking at alternatives for another platform. I would be great if you could point me in the direction of a good platform.

Hello there! Quick question that's entirely off topic. Do you know how to make your site mobile friendly? My web site looks weird when viewing from my iphone 4. I'm trying to find a theme or plugin that might be able to resolve this issue. If you have any recommendations, please share. With thanks!

I definitely wanted to compose a quick message to be able to say thanks to you for the remarkable advice you are posting on this website. My long internet search has now been rewarded with excellent information to exchange with my family members. I 'd express that we readers are undoubtedly fortunate to dwell in a superb community with so many outstanding people with valuable secrets. I feel truly lucky to have used your entire weblog and look forward to plenty of more cool moments reading here. Thanks a lot once more for a lot of things.

Hello There. I found your blog using msn. This is an extremely well written article. I抣l make sure to bookmark it and return to read more of your useful info. Thanks for the post. I抣l definitely comeback.

A few of the special gems can be bought in a whole new advertising for favorite retail outlet H&M's daughter brand, & Different Testimonies,<a href="http://cheaparmanipolotshirtsoutlet.com/&quot;>armani t shirts</a>,which in turn introduced the Oughout.Verts. retail store not too long ago. These display colourful patterns via Clare Vivier's different Co-Lab series, the features boots or shoes, luggage along with components and are available for sale concerning March Several.

Several females can be bought in an innovative advertising intended for well-known store H&M's mother model, & Other Tales,<a href="http://www.cheaparmanioutletonline.com&quot;>cheap armani outlet</a>,in which presented any Anyone.Ersus. online website recently. Many people showcase bright colored patterns from Clare Vivier's fresh Co-Lab assortment, featuring shoes, handbags and even products and will also be made available now available about Mar Your five.

Getting some exercise is a big section of living at this point. I just truly like sweating over,<a href="http://www.elliottsweb.co.uk/&quot;>tiffany jewellery outlet</a>,this problems plus existing your health. My personal workouts are don't assume all related to vanity. They can be related to purity for my imagination and even conscience. Everybody has distinct our educational career in your life, be sure that your voyage is good for you and you by itself.

The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought youd have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention.

Yet another thing is that while looking for a good on-line electronics store, look for online shops that are consistently updated, always keeping up-to-date with the newest products, the perfect deals, and helpful information on products. This will make sure that you are getting through a shop that really stays on top of the competition and provide you things to make knowledgeable, well-informed electronics acquisitions. Thanks for the significant tips I've learned from the blog.

Hi, I believe your blog could be having web browser compatibility problems. When I look at your site in Safari, it looks fine however, when opening in I.E., it has some overlapping issues. I just wanted to give you a quick heads up! Other than that, great site!

After I initially left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I get four emails with the exact same comment. There has to be a means you can remove me from that service? Thank you!

Howdy, I think your site could be having internet browser compatibility problems. Whenever I look at your site in Safari, it looks fine but when opening in I.E., it has some overlapping issues. I merely wanted to give you a quick heads up! Apart from that, excellent blog!