Geeks With Blogs
Jim Giercyk

All DBAs have been asked to copy a database from one SQL instance to another.  Even if the instances are identical, the relationship (SID) between the logins and table users are broken.  Microsoft includes sp_change_users_login to help mitigate this problem, but using the stored procedure must be done database by database, or even user by user, which can take a significant amount of time.  Consider the time it takes to migrate an entire server, or create a secondary instance.

 

Why not automate the process.  Instead of taking hours, the following Powershell script will fix the orphans first, and then drop any table users that do not have a corresponding login.  You’ll be done in seconds instead of hours…..but that will be our little secret.  Winking smile

 


IMPORT-MODULE -name SQLPS -DisableNameChecking

$Server = 'Myserver'  
$ProdServer = new-object ('microsoft.sqlserver.management.smo.server') $server
$ProdLogins = $ProdServer.Logins.name


foreach ($database in $prodserver.Databases)
{
$db = $database.Name.ToString()
$IsSystem = $database.IsSystemObject
$ProdSchemas = $database.Schemas.name

    #######################################
    # Auto-Fix Orphaned user with a login                #
    #######################################
    foreach ($user in $database.users | select name, login | where{$_.name -in $ProdLogins -and $_.login -eq ""}) 
    {
    $sqlcmd = "USE " + $db + " EXEC sp_change_users_login 'Auto_Fix','" + $user.name + "'"
    invoke-sqlcmd -query $sqlcmd -serverinstance $Server
    }

    #######################################
    # Drop table users that have no login                 #
    #######################################
    foreach ($user in $database.users | select name, login | where{$_.name -notin $ProdLogins -and $_.name -notin $ProdSchemas -and $_.login -eq "" -and $db -notin ('SSISDB','ReportServer','ReportServerTempDb') -and $IsSystem -eq $false})
    {
    $sqlcmd = "USE [" + $db + "]
        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'" + $user.name + "')
        DROP USER [" + $user.name + "]
        GO"
    invoke-sqlcmd -query $sqlcmd -serverinstance $Server
    }
}


 

In this example, I’ve excluded system databases and the SSRS and SSIS databases.  You can include/exclude any databases you want.  A version of this script has helped me with numerous server migrations, and continues to be a way to keep production instances in sync.  I’ve also used it to do nightly production refreshes to a development environment without causing problems for the users.  Give it a try, but don’t forget to add some logging (I removed mine in the example to simplify the code.).  So get going….adopt an orphan!

Posted on Thursday, May 4, 2017 10:08 AM | Back to top


Comments on this post: Adopt a SQL orphan

# re: Adopt a SQL orphan
Requesting Gravatar...
Very helpful, very informative article. This site has lots of information and it is useful for us. Thanks for sharing the post - chennai to shirdi flight package, shirdi tour package from chennai

http://chennaitoshirdiflight.com/chennai-to-shirdi-flight-package.html
Left by Sri Sairam Subhayatra on May 10, 2017 5:11 AM

# fireboy and watergirl game
Requesting Gravatar...
It was a great pleasure to play fre fireboy and watergirl gamefrom the online website that we share and for you to play
fireboy and watergirl online game and enjoy with the gaming environment. of free fireboy and watergirl game online.
Left by ford on May 30, 2017 8:34 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
It takes a great deal of job, particularly with the location check out profits eliminated, designers usually do not begin making roblox hack up until they have more than 10k location check outs. We are providing Roblox Robux Generator which is simple to utilize as well as could make you a leading gamer since we desire your players to not experience such issues and also troubles.
Left by arijitydv on Jun 26, 2017 2:33 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Sugar Maths is a brand-new video game that make certain that you discover how to fix math games troubles rapidly in your mind without any errors.
Left by amanyadav on Jun 26, 2017 2:38 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Hi you people I am working for a http://resume-writing-services.reviews/ blog as database admin for their oracle server. I use to face the same problem in migrating the data from one database server to other operating system database server. Thank you for sharing this blog post which has given me an idea how to deal with that problem.
Left by Fred K. Lunde on Jul 06, 2017 10:57 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
I would like to try this shell script with my own kind of trial and let you know how it worked with my http://essaymama.org/ blog. So that it might be like an experiment to make and get succeeded.
Left by Curtis M. Black on Jul 10, 2017 8:13 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
How in the world could you achieve such speeds, it’s unbelievable. I would really like to try this out next time. Thanks for sharing the code here so that everyone could try this. The step by step procedure really helped out to complete the whole process. California Criminal Defense
Left by Taylor Shaw on Aug 01, 2017 8:31 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Download nds4droid roms from here nds4droid roms download
Left by Gari on Aug 09, 2017 11:35 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
thank you so much geekswithblogs , good luck
godcccam
Left by Mina on Aug 23, 2017 2:57 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
automation is the trend nowadays.. with all the giant making their entry with the AI it is going to be a tough competition.
Commoners are going to sit relax with this competition I guess.
Left by Tom on Aug 25, 2017 4:01 AM

# game
Requesting Gravatar...
Hellow guys this site is my favorite, we loved Play mahjongg dark dimensions online game on pc,friends you can play this game directly your pc browser so click this link http://mahjongfreegames.online/dark-dimensions mahjongg game is very nice and minded game, guys am also using this game this site,
Left by play game on Sep 01, 2017 6:54 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Roblox is the world's largest social platform for play. Every month, over 48 million players imagine, build, and play together within immersive 3D worlds. roblox hack
Left by mark antony on Sep 15, 2017 1:32 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
I would really like to try this shell script with my own form of trial and can help you understand the way it worked with my essay help weblog. in order that it might be like an test to make and get succeeded.
Left by Daisy Faith on Oct 02, 2017 12:57 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Is it really possible to achieve such speeds while using the application? This is quite hard to believe. Anyway I will definitely try these steps as you have provided the whole details with the codes here. keep sharing more of them. www.meritotreatment.com
Left by lionel john on Oct 03, 2017 6:45 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
I’m in love with this web site. I have visited this site so frequently.
I discovered this web site on the internet. I have gotten a good stuff of information. Many thanks. keep updating me..
http://acer-support.uk/acer-helpline-number/

Left by Soya on Oct 05, 2017 2:50 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
We all want to have the roblox hack online here.
Left by Siddhant on Oct 12, 2017 11:31 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
I like this post so much and great work is done on this post and all the details are in this post is awesome. Independent Call Girls Delhi
Left by Alisha on Nov 06, 2017 4:33 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
its not working help me....cream pemutih wajah yang bagus
Left by andrew on Nov 13, 2017 10:47 PM

# re: Adopt a SQL orphan
Requesting Gravatar...
Is it really work? I am going to try my own website Mobile Signal Booster India. If it will work, I will post my own experience here..Thanks for sharing this valuable information.Mobile Signal Booster in Delhi
Left by Poonam Singh on Dec 21, 2017 11:51 PM

# re: Adopt a SQL orphan
Requesting Gravatar...
We are from Signal Booster India.

We visit your website and read you topic. We are really like this topic. You are writing absolutely true that how to manage Sql Database. It’s great.

We are network service provider and trying to blog comment related to mobile network booster.
Left by vijay mishra on Dec 29, 2017 11:16 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Will these feature help to improve the speed of the database? I am not an expert in dealing with all these codes and programming charts. Anyway I will definitely try using the SQL orphan when dealing with the process next time. tour amsterdam
Left by lionel on Jan 16, 2018 6:35 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
I really like your point you have written very amazingly. Thanks for sharing your information with everyone and keep posting
cool math games
Left by Anna Shetty on Jan 17, 2018 2:53 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Thanks for the article.

zara ansari
Left by Tom on Jan 24, 2018 5:23 PM

# re: Adopt a SQL orphan
Requesting Gravatar...
This is Signal Booster Mumbai. We've seen your article, in fact this is very interesting. Actually this feature will help improve the speed of the database. Many thanks for sharing this good information. Mobile Signal Booster Mumbai .
Left by Brijesh on Mar 07, 2018 12:23 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
It is quite possible to achieve faster speed with the use of this application. The code you provided has provided information. Very good. We will use the SQL orphan. Network Booster Mumbai
Left by Shayam on Mar 07, 2018 12:53 AM

# re: Adopt a SQL orphan
Requesting Gravatar...
Left by tun on Mar 26, 2018 11:10 AM

# Bangalore Call Girls
Requesting Gravatar...
I like your articles writing skill, it's an amazing man and every sentence has too much info. Well, keep writing and posting.
http://www.mumbaiescorts4u.in
Left by Tipsy Bajaj on Apr 16, 2018 8:39 AM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net