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.
And User2 is owner of role NewDBRole01 (that I have created for this example)
When you try to drop an orphaned user that has associated objects, you will get the following error messages:
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:
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:
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
- Test out this script in a Development or Test environment and validate all of the conditions are met in your environment.
- Read these related tips:
