SQL Server – Drop An User In All Databases & Drop The Login Too!

I am a strong proponent and a believer of using AD groups (not even AD users) to manage your SQL Server permissions end to end. Restated, you would not have any individual AD users as logins in your SQL Server instance, all instance logins with be AD groups. If you are dealing with plain SQL logins (not AD/Windows users), I am truly sorry!

Using AD groups not only forces discipline in your privilege management but also when the employee quits or when a new employee with similar work function arrives, it is just a matter of making the change in one place – Active Directory and everything else just works!

Dropping a database user in all databases and then their login – Using sp_MSForEachDB

Assuming you do not manage your SQL Server security with AD groups but you rather do it with individual users, the below snippet should help you drop the user in every database and then drop the corresponding login itself. Note that empty schemas get dropped too.

--Drop the schema's. They WILL NOT get dropped if schema had objects
EXEC sp_MSForEachDB 'USE [?];
		IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N''USER_TO_DROP'')
		DROP SCHEMA [USER_TO_DROP]; '

--Drop the database users
EXEC sp_MSForEachDB 'USE [?];
		IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N''USER_TO_DROP'')
		DROP USER [USER_TO_DROP]; '

Now, if you did not run into any errors doing the above, then run this. DO NOT rush to run the below SQL if you ran into errors above!

--Drop the login
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'USER_TO_DROP')
	DROP LOGIN [USER_TO_DROP];

Note: The reason I separated the two above is because SQL Server will be happy to drop your login without dropping the database users (due to errors) leaving orphaned users. Login drop will only fail only under this situation (from documentation) “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.”

Errors

Along the way, you may run into errors such as below:

Cannot drop schema ‘USER_TO_DROP’ because it is being referenced by object ‘Test’.

Msg 3729, Level 16, State 1, Line 3
Cannot drop schema 'USER_TO_DROP' because it is being referenced by object 'Test'.

The above error means that the schema has objects. In this case the user USER_TO_DROP references an object (table in this case) named Test. Simply change the schema of the object to “dbo” if that is valid in your case and then drop the schema.

--We are changing the schema of table Test from "USER_TO_DROP" to "dbo"
SELECT name, 'ALTER SCHEMA dbo TRANSFER ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) AS Command
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = 'USER_TO_DROP'

--Sample commands generated
--ALTER SCHEMA dbo TRANSFER USER_TO_DROP.Test

--Now the "schema" USER_TO_DROP can be dropped!

This should give you the list of objects you need to change schema’s for. You can copy and run the commands returned by the “Command” column above.

Caution: There is a reason I did not include this as part of the main script to drop everything. This may break your application functionality and should only be done if you are absolutely certain that the user you are trying to drop can go!

The database principal owns a schema in the database, and cannot be dropped

You might get this error when you try to drop a database user

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

This error just means that the database user owns one or more schemas. We just have to identify the schemas that the user owns and transfer ownership of the schemas to another user (like dbo) and then drop the user.

SELECT name,  'ALTER AUTHORIZATION ON SCHEMA::' + name + ' TO dbo; ' AS Command
FROM sys.schemas s WHERE s.principal_id = USER_ID('USER_TO_DROP');

--Sample commands generated
--ALTER AUTHORIZATION ON SCHEMA::USER_TO_DROP TO dbo;

--Now the database user USER_TO_DROP can be dropped!

This should give you the list of schemas you need to transfer ownership for. You can copy and run the commands returned by the “Command” column above.

Please post your ideas and comments below if you find this useful or have suggestions.

Advertisement