Script to Drop All Orphaned SQL Server Database Users

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

Related Posts

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *