Orphaned SQL users

problem

Recently many of the company's databases are on the cloud, and there is also a part of the next cloud. There were a lot of problems, and one of the more disgusting problems was "orphaned users". When a database backup is restored, it cannot be logged on with the previous user discovery. At first thought that the login account did not create, and then re-create the login account, and then authorized to the database, and then error, said the user already exists. I got noticed and started searching for this information, which was caused by notorious isolated users.

What is an orphaned SQL user?

So what's an isolated user? An orphaned user is a database user with no logon rights to SQL Server.

There are many reasons for orphaned users in real-world production, and the most important way is when a backup is restored to a different server instance. When restoring the database, the database and the user are restored together to the new database, but the server's login account is not restored together (and does not need to do so). If the database is the same server then happy, because the user has not changed. If it is a different server, the login account does not have the name of the database, even if you create the same name but their ID is also different cause they can not be associated. This causes the user of the database to be orphaned and inaccessible. All we need to do at this point is isolate the user to modify or delete the rebuild.

is a list of possible causes of orphaned users in foreign netizens (in detail):

Find orphaned users in a database

I'm going to write a script to achieve two main purposes, one is to find all the orphaned users in one instance, and the second is to delete these users on demand. From the Internet to find a lot of script and blog discovery can not be achieved. So I wrote myself a pro test available. The trouble with this script is that when the user is deleted, the user has his or her own object and cannot drop it, only to delete the object or to change the relationship between the object and the user. In the example below, all users have a schema, and the script has to deal with the user's schema. The script implementation stores the orphaned user in a temporary table, and then deletes the schema and the user based on the user information of the temporary table.

Find a script for orphaned users

 UseMasterGoCreate Table#Orphans (RowIDint  not NULL Primary Key Identity(1,1), Tdbnamevarchar( -), UserNamevarchar( -), UserSIDvarbinary( -) )SETNOCOUNT on  DECLARE @DBNamesysname@Qry nvarchar(4000) SET @Qry = "' SET @DBName = "'  while @DBName  is  not NULL BEGIN   SET @DBName =      (  SELECT MIN(name) fromMaster.. sysdatabasesWHERE   /** To exclude named databases add them to the ' not ' in clause **/name not inch      (      'Model','msdb',       'Distribution'     )  and      Databaseproperty(Name,'IsOffline')= 0       and Databaseproperty(Name,'IsSuspect')= 0       andName> @DBName      )   IF @DBName  is NULL  Break                         Set @Qry = 'Select" " + @DBName + " "As DBName, name as UserName, sid as UserSID from [' + @DBName + '].. sysusers where Issqluser = 1 and (SID is not null and Sid <> 0x0) and SUSER_SNAME (SI D) is the null order by name' Insert  into#OrphansExec(@Qry)  EndSelect *  from#Orphans

How to delete a user (this section remembers to use it as appropriate, confirming that the orphaned user has already used the deletion before using the person or DBA.) and verify that the schema object is not affected)

Then the above script, we take the user out of the temporary table for the loop processing.

 
 Declare @SQL as varchar (Declare @DDBName varchar) Declare @Orphanname varchar (100) Declare @DBSysSchema varchar Declare @From intdeclare @To intSELECT @From = 0, @To = @ @ROWCOUNT from #Orphans--print @From--print @ToWhile @From <= @To Begin Set @From = @From + 1 Select @DDBName = tdbname, @Orphanname = UserName fr Om #Orphans Where RowID = @From Set @DBSysSchema = ' [' + @DDBName + '] ' + '. [SYS]. [Schemas] ' Print @DBsysSchema print @DDBname print @Orphanname set @SQL = ' If Exists (Select * from ' + @DBSysSche                                         Ma + ' WHERE name = ' ' + @Orphanname + ') Begin use ' + @DDBName  + ' Drop Schema [' + @Orphanname + '] End ' Print @SQL Exec (@SQL) Begin Try Set @SQL = ' use ' + @DDBName + ' Drop User [' + @Orphanname + '] ' Exec (@SQL) End T Ry Begin catch end Catch End  Drop Table#Orphans

Things to keep in mind in scripting

First, if some of the database's orphaned users do not want to process it, you can exclude the database in advance through the not in statement when inserting the temporary table. Orphaned users who are deleted also delete schemas owned by orphaned users. Attention needs to be drawn. This script will not check for other objects that may be owned by the user. I have tested it on SQL Server 2005/2008/2014, please be aware of it.

Summarize

When it comes to cloud or database migrations, there are often some unpredictable problems that can arise when you find such errors, I suggest. Queries can be queried to see which users are orphaned, what needs to be treated differently, and other processes. The script has greatly reduced its time to query, but also increased the risk, please use as appropriate.

Orphaned SQL users