By: Percy Reyes | Last Updated: 2014-12-11 | Comments (8) | Security
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.
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)[email protected] 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::['[email protected]_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)[email protected] 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::['[email protected]_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)[email protected] 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'''[email protected]_name+''', N'''[email protected]+'''' 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 ['[email protected] +']' 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.
- 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:
Last Updated: 2014-12-11
About the author
View all my tips