How To Fix Windows Orphaned Users In Sql Server

Introduction

Context

As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user.

In that location are exceptions similar the two following ones:

1. LoginA has very high permissions at server level like being fellow member of sysadmin role
2. Db1 is cocky-contained, which means that it uses its own authentication and does not bargain with SQL Logins. (SQL Server 2012+)

Nosotros might call back that this linkage is done by matching names, but it'due south not the instance. Normalization comes in and this mapping is actually stored using a unique identifier called "SID":

We can check SIDs for all our server principals using the following query :

select principal_id , [ sid ] , name from sys . server _principals ;

If we look at documentation page of the sys.server_principals view, we can see that :

• The principal_id is like an identity column. So, if we drib a login and recreate information technology, the principal_id won't exist the aforementioned.
• The sid column defines a security identifier for the primary that volition exist the same for Windows authenticated logins. This means that if we use mixed authentication and nosotros create a login then drop and create it again, information technology won't take the same SID.

If a mapping is defined for a given SQL Login, so its corresponding SID can exist constitute in sys.database_principals using the following query:

select [ sid ] , name from sys . database _principals

For simplicity, it'due south common, but not mandatory, to employ the verbal same proper noun for both SQL Login and Database User.

As you could expect, this linkage can be "broken" in many different ways that makes this definition "inconsistent", that leaves database users without whatsoever link to a login. When this happens, we refer to such a database user every bit an orphaned database user.

The situation is summarized in post-obit figure.

Permit'south enlist some utilise cases that lead to orphaned database users:

• Dropping a SQL Login using SSMS, fifty-fifty though we are well aware that this will happen equally SSMS shows a warning:

  

• When using mixed authentication style, restoring a database backup to another SQL Server instance.
• Subsequently a database mirroring failover.

As y'all tin meet higher up, there are two possible ways to manage these orphaned users:

1. Drop them as they are no longer needed
2. Link them back to the SQL Server login with the advisable proper name

In the post-obit sections, we will see how to listing those orphaned database users and how to handle both cases listed above. Finally, we will review stored procedures that will be helpful in that state of affairs.

Orphaned Database User discovery

To be able to get a list of orphaned users for every databases of a given SQL Server instance, you have to run the following argument confronting each of them:

Employ [ YourDbName ] ; GO exec sp_change_users _login @ Activity = 'Report' ; GO

This stored procedure will return a ii-columns dataset with firstly the proper name of an orphaned database user and secondly its respective security identifier.

Alternatively, we can get the list of SIDs that are divers in sys.database_principals but not in sys.server_principals as follows:

select p . name , p . sid from sys . database _principals p where p . type in ( 'Chiliad' , 'S' , 'U' ) and p . sid not in ( select sid from sys . server_principals ) and p . proper noun non in ( 'dbo' , 'guest' , 'INFORMATION_SCHEMA' , 'sys' , 'MS_DataCollectorInternalUser' ) ;

Handling an orphaned database user

As we said previously, there are 2 ways to handle orphaned database users: either we drop or remap them.

Database User Drib

Dropping a database user seems pretty straight forrard: nosotros merely need to run the DROP USER statement and it's over. Just what if this database user owns objects inside the database? It's not that simple…

If it's actually the activity we want to perform, nosotros volition need not just to accept a expect at that aspect and correct it, but also to cheque that this user is not used as the execution context of one or more stored procedures, functions or upshot notifications. Here is the fault we could go if that's the case:

Msg 15136, Level 16, State 1

We will also demand to remove group membership of that user and in that location are maybe some other aspects that I don't comprehend it because I've never been confronted to them.

An assay of these aspects can be quite long and has to exist done for all orphaned users. Plus, this activeness tin can be used in other database management tasks. So, this is a good candidate for automation and you volition find fastened to this article a script with a stored procedure that takes care of dropping a database user and performs some pre-checks earlier that. The script is called

« Procedure.Administration.DropDatabaseUser.sql »

Allow's review the details of this stored process.

Offset, allow's see its parameters

Change Procedure [ Administration ] . [ DropDatabaseUser ] ( @ DbName VARCHAR ( 256 ) , @ UserName VARCHAR ( 256 ) , @ ResetOwnership Scrap = 1 , @ NewObjectOwner VARCHAR ( 256 ) = 'dbo' , @ Debug Fleck = 0 )

As you can see, it takes the proper noun of the database in which nosotros want to perform the drop and the proper noun of the database user nosotros want to drop. There is a flag scrap to tell the process to assign database objects owned past @UserName to another database user @NewObjectOwner. By default, this user is dbo. Finally, at that place is a flag bit that will enable a debug mode and make this procedure more than talkative.

The commencement function of the stored procedure is a set of pre-requisities checks :

• Check that database exists
• Bank check that database user exists
• Check that the object owner for @UserName's object exists in the database, only if @ResetOwnership parameter is set to 1.
• Bank check that the database user nosotros want to drop is non a MS-Shipped user like dbo or INFORMATION_SCHEMA.
• Check modules with an EXECUTE Every bit statement that refers to the database user we want to driblet. If whatsoever module is found, it'south returned by a SELECT argument.

Then information technology will go the list of owned schemas and owned roles and enhance an fault whenever @ResetOwnership parameter is not set up to 1 and information technology founds something.

Finally it volition stack roles of which @UserName is member.

Once the list is complete, it volition loop and either :

• Put an stop to role membership using sp_droprolemember stored procedure
• Transfer buying of schema or role using an Change Authorization statement

When all that is done, we tin can finally run the DROP USER statement.

Database User Remapping

For remapping, nosotros must use the sp_change_users_login stored procedure we already used to report orphaned users.

We can run information technology for a single database user mapping equally follows:

EXEC dbo . sp_change_users _login @ Action = 'update_one' , @ UserNamePattern = 'UserA' , @ LoginName = 'LoginA' ;

Alternatively, we can try the autofix parameter :

EXEC dbo . sp_change_users _login 'auto_fix' , 'UserA' ;

But running this requires that the name of SQL Login to be the exact same as the database user we desire to remap.

This process also allows to create the login when it doesn't be.

EXEC dbo . sp_change_users _login 'auto_fix' , 'UserA,                                cypher,' aVeryStrongP @ ssw0rd' ;

In any of the methods presented above, this has to be performed one database at a time.

Example-level management of orphaned users

Components of the solution

In the previous section, we saw how to manage orphaned users for one database at a time. While this is good for testing purpose, we might remember that it'south non affordable for production environments where we sometimes have 10 or more databases that take been restored and that we must take care of.

That's the reason why I developed a stored procedure chosen Administration.FindOrphanUser that will loop across all databases of the instance and optionally try to set up orphaned users (which means remapping) and/or driblet them.

It's congenital on a stored procedure called Common.RunQueryAcrossDatabases that runs a T-SQL argument beyond a fix of databases. It's like the undocumented sp_MSforeachdb stored procedure, but with its differences.

Both Administration.FindOrphanUser and Mutual.RunQueryAcrossDatabases procedures are attached to this article.

As it's not the aim of this commodity, I won't insist on Mutual.RunQueryAcrossDatabases procedure and we will focus on the other 1.

And so, what's its interface? Information technology takes 5 parameters:

• an optional database name
• a flag scrap to tell whether or non try to remap database users to a login of the same name
• a flag scrap to tell whether or not try to drop remaining orphaned database users (afterward the trial to remap has been washed)
• a parameter to tell whether to get the listing of orphaned users or an execution report. Two possible values then: 'Report' or 'Tabular array'. All other values than these ones are equivalent to a 'NONE' output value and no dataset will be returned by the stored procedure.

This gives the post-obit in T-SQL:

ALTER PROCEDURE [ Administration ] . [ FindOrphanUsers ] ( @ DatabaseName VARCHAR ( 256 ) = NULL , @ TryToFix BIT = 0 , @ DropUnfixable Fleck = 0 , @ OutputType VARCHAR ( sixteen ) = 'TABLE' , @ Debug Chip = 0 )

This stored procedure volition create a temporary table #OrphanUsersData that will contain all the data (and a little more) that are manipulated during execution.

Here is the creation statement for this table.

CREATE Table #OrphanUsersData ( DbName VARCHAR ( 256 ) , UserName VARCHAR ( 256 ) , UserType VARCHAR ( 256 ) , DefaultSchemaName VARCHAR ( 256 ) , CreationDate Date , ModifiedDate Appointment , FixIssue VARCHAR ( xvi ) , DropIssue VARCHAR ( 16 ) , DDL2Drop VARCHAR ( MAX ) , DDL2Remap Equally 'Use ' + QUOTENAME ( DbName ) + ';' + CHAR ( 13 ) + CHAR ( ten ) + 'EXEC dbo.sp_change_users_login @Action = ' 'update_one' ', @UserNamePattern = ' '' + UserName + '' ', @LoginName = ' '' + UserName + '' ';' + CHAR ( thirteen ) + CHAR ( 10 ) ) ;

Let'due south review each column.

We have obviously columns for database proper noun and user name. Then nosotros take some full general information on that user:

• The UserType column should exist either SQL_USER or WINDOWS_USER and corresponds to the type_desc column in sys.database_principals.
• The name of the default schema. It's truthful nosotros didn't mention it previously, but each database user has a default schema (which is, in my experience and unfortunately most of times dbo).
• Its cosmos and last modification dates

The adjacent two columns are related to the execution of Administration.FindOrphanUser stored procedure. It will contain the outcome of the execution that is implied past @TryToFix and @DropUnfixable parameters.

Finally, nosotros accept the terminal two columns that will contain DDL statements. The kickoff one will be used to drop the database user (it volition actually be a phone call to former [Administration].[DropDatabaseUser] stored process). The second one will contain the statement for database user to login re-mapping. As this statement is pretty simple, we created it as a computed column.

This tabular array will be used every bit destination in the query we will provide to Mutual.RunQueryAcrossDatabases stored procedure. Actually, this query will exist formed as follows:

INSERT INTO #OrphanUsersData < Select Statement that uses DB_NAME ( ) congenital - in function >

In one case this stored procedure has completed the discovery of orphaned database users, if @TryToFix parameter is ready to 0, then information technology wil loop on each database user and run the corresponding DDL2Remap statement. The outcome of this operation will exist stored in FixIssue cavalcade.

Same goes for the database user removal if @DropUnfixable is prepare to 1.

Finally, the stored process returns a dataset that is either the content of #OrphanUsersData temporary table or a report based on values of FixIssue and DropIssue columns.

How to install?

Prior to installation, you lot must beginning select or create a database in which you will install this solution. You must too create following database schemas:

• Common
• Administration

You volition find below the listing of files that are attached to this article and must exist run in that order.

• Blazon.Common.DatabaseNameList.sql
• Process.Common.RunQueryAcrossDatabases.sql
• Procedure.Assistants.DropDatabaseUser.sql
• Procedure.Assistants.FindOrphanUsers.sql

How to apply the solution?

This is, to me, the best way to manage orphaned users. Yous will find beneath the way I recommend to use these stored procedures.

Showtime, run with defaults. This will just list all orphaned users with everything we demand to fix the situation.

EXEC [ Administration ] . [ FindOrphanUsers ] @ Debug = 0;

This telephone call should likewise exist automated so that a notification is sent to database administrators when 1 or more than orphaned users are found. This automation could also be used by checking the TotalCount cavalcade of the dataset returned by post-obit call:

EXEC [ Assistants ] . [ FindOrphanUsers ] @ OutputType = 'Report' , @ Debug = 0 ;

If some orphaned users are reported, then nosotros tin can run the procedure with @TryToFix parameter set to 1:

EXEC [ Administration ] . [ FindOrphanUsers ] @ Debug = 0 , @ TryToFix = 1

If whatsoever error is returned by the process, check information technology and try to solve. Don't hesitate to send me a bulletin with details of the error and so that I can try to update these stored procedures to the state of affairs you found.

I don't recommend running with @DropUnfixable parameter set to i if you are non sure of what you are doing.

• Author
• Contempo Posts

Living in Belgium, I obtained a chief degree in Computer Sciences in 2011 at the University of Liege.

I'1000 one of the rare guys out there who started to piece of work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot almost SQL Server in administration and evolution.

I like the job of DBA because you need to take a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Source: https://www.sqlshack.com/how-to-discover-and-handle-orphaned-database-users-in-sql-server/

Posted by: lipskyfronew.blogspot.com

Share this post