Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Script to Drop All Orphaned SQL Server Database Users


By:   |   Read Comments (6)   |   Related Tips: More > Security

Problem

One important task as a DBA is to manage security in our SQL Server database server environments. Part of this task is to administer the permissions for each user in each database. Sometimes there are orphaned users (no corresponding login exists for the user) in a database and we may want to drop these orphaned users to keep the SQL Server database security clean. Unfortunately, there are several items that can cause a drop user script to fail and in this tip I will share a simple and complete script to drop orphaned users successfully.

Solution

An orphaned user is a SQL Server database user that does not have an associated login (Windows login or SQL login) at the SQL Server instance level. This could cause problems related to access and permissions or these users may not be needed any more.  Based on these circumstances, so the user should be removed from the database. You can read more about SQL Server orphaned users here.

There are many reasons why we can have orphaned users inside a database. Here are some of them:

  • The DBA received a request to remove a database user and only deleted the login.
  • The database was migrated/restored in a new SQL Server instance and not all the logins were migrated.
  • The DBA received a request to create a new database user and created a user without a login.

Dropping the user has bigger problems when the orphaned user has associated objects inside the database (for example schemas and database roles). I have found many SQL Server users that are owners of schemas and/or database roles, so to drop the user you will first need to remove this association or dependency and then you can are able to drop the orphaned user. It is important to note that in SQL Server 2012 and later versions we can create Contained SQL Server database users with a password that is authenticated by the database and there is not a need for a Login. The script below to drop orphaned users considers this concept and discriminates this type of user. You can read more about Contained Databases here.

Note: In SQL Server 2012 and later contained databases support contained Windows user, which do not need an associated with a Windows login. There is not an exact way to determine if a Windows user is being used in a contained database, so for these cases you must be aware that these Windows users will be considered as orphaned users by the script below and they will be dropped. So, please be careful if you are working with SQL Server Contained Databases.

Understanding Issues when Dropping Orphaned SQL Server Users

To explain better I have created the following example: two orphaned users were created named User1 and User2, both do not have an associated login (they are not contained database users and they are "regular" database users). The user User1 owns schema db_datareader.

MSSQLTips_How to drop all orphaned database users via a simple script

And User2 is owner of role NewDBRole01 (that I have created for this example)

MSSQLTips_How to drop all orphaned database users via a simple script

When you try to drop an orphaned user that has associated objects, you will get the following error messages:

MSSQLTips_How to drop all orphaned database users via a simple script
MSSQLTips_How to drop all orphaned database users via a simple script

Identify Orphaned SQL Server Users

With the query below we can identify the orphaned users we need to drop:

select DB_NAME() [database], name as [user_name], type_desc,default_schema_name,create_date,modify_date from sys.database_principals 
where type in ('G','S','U') 
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

The output will be as follow:

MSSQLTips_How to drop all orphaned database users via a simple script

SQL Server Script to ALTER Permissions and Drop Orphaned Database Users

After we know which users we will drop, the below script can be used to drop the orphaned users taking in account the need to first remove the association to schemas and database roles.

use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
 set nocount on
 -- get orphaned users  
 declare @user varchar(max) 
 declare c_orphaned_user cursor for 
  select name
  from sys.database_principals 
  where type in ('G','S','U') 
  and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
  and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 
  and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')  open c_orphaned_user 
 fetch next from c_orphaned_user into @user
 while(@@FETCH_STATUS=0)
 begin
  -- alter schemas for user 
  declare @schema_name varchar(max) 
  declare c_schema cursor for 
   select name from  sys.schemas where USER_NAME(principal_id)=@user
  open c_schema 
  fetch next from c_schema into @schema_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_schema varchar(max)
   select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'
   print @sql_schema
   exec(@sql_schema)
   fetch next from c_schema into @schema_name
  end
  close c_schema
  deallocate c_schema   
  
  -- alter roles for user 
  declare @dp_name varchar(max) 
  declare c_database_principal cursor for 
   select name from sys.database_principals
   where type='R' and user_name(owning_principal_id)=@user
  open c_database_principal
  fetch next from c_database_principal into @dp_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_database_principal  varchar(max)
   select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'
   print @sql_database_principal 
   exec(@sql_database_principal )
   fetch next from c_database_principal into @dp_name
  end
  close c_database_principal
  deallocate c_database_principal
    
  -- drop roles for user 
  declare @role_name varchar(max) 
  declare c_role cursor for 
   select dp.name--,USER_NAME(member_principal_id)
   from sys.database_role_members drm
   inner join sys.database_principals dp 
   on dp.principal_id= drm.role_principal_id
   where USER_NAME(member_principal_id)=@user 
  open c_role 
  fetch next from c_role into @role_name
  while (@@FETCH_STATUS=0)
  begin
   declare @sql_role varchar(max)
   select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''
   print @sql_role
   exec (@sql_role)
   fetch next from c_role into @role_name
  end
  close c_role
  deallocate c_role   
      
  -- drop user
  declare @sql_user varchar(max)
  set @sql_user='DROP USER ['+@user +']'
  print @sql_user
  exec (@sql_user)
  fetch next from c_orphaned_user into @user
 end
 close c_orphaned_user
 deallocate c_orphaned_user
 set nocount off
end
go
-- mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go

Here is how to execute the stored procedure for one specific database:

USE [MyTestDB]
GO
EXEC sp_Drop_OrphanedUsers

Here is how to execute it for all databases in a SQL Server instance:

USE [master]
GO
EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'

After the stored procedure runs, you will see the actions taken by the script:

MSSQLTips_How to drop all orphaned database users via a simple script

We know each database environment is different, so you may find others dependencies that you need to add into this script. However, this script does work very well for most environments.

Next Steps


Last Update:






About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, June 08, 2016 - 2:29:13 AM - akhil Back To Top

Hi  Percy Reyes,

Need your help i want to create the procedure to drop the orphan users.

when we create a database and logins with the same name when we drop the database the logins become orphan ( we will give only the db_owner permission only on that particulare database ) 

and cause security issue 

and i used the below queries to see if i can accomplish some thing else

 

Exec sp_databases  or  select * from sys.databases ( to check the databases ) 

 

( to check the user ids present in the server which contains all the databases ) 

 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'S'

and name not like '%##%'

 

i am an oracle DBA and this is the first step towards SQL please do help me with this i am eagerly waiting for an answer.

 

Thanks in advance.

Regards,

Akhil

 

 


Thursday, February 25, 2016 - 9:46:46 AM - Desiree Haywood Back To Top

 Does not work for "read-only" databases.

 


Tuesday, March 17, 2015 - 11:01:31 AM - Sukanta Das Back To Top

Excellent utility proc, thanks so much.


Monday, December 22, 2014 - 11:07:10 AM - Percy Reyes Back To Top

Hello SurendraP,

This script does not support users created for Database Impersonation by Using EXECUTE AS. I suggest first go deep into sys.sql_modules and check what users are used for Database Impersonation  and then filter them.

Let me know any comment or questions if you have one. Thanks.

 

Regards,

 


Friday, December 19, 2014 - 3:30:44 PM - SurendraP Back To Top

But I am gettign this error message while doing so:-

 

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

 

Please suggest.

Thanks 


Thursday, December 18, 2014 - 12:45:07 PM - Mohammed Back To Top

Awesome, just tried this today and it worked like a charm. Thanks a bunch.


Learn more about SQL Server tools