Dropping a User or Role with Objects

At some point, we get asked to delete a user or role for a particular database. For example, we want to drop the barney_user account from the company1 database.
When we run the following script:

use company1;
go
drop user barney_user;

We get the error:
The database principal owns a schema in the database, and cannot be dropped.
So, we decide to drop the schema as well:

use company1;
go
drop schema Sales; 

And then we get the error:
Cannot drop schema ‘Sales’ because it is being referenced by object ‘Person’.
It seems that the Sales schema has a table called Person which we should not delete. Management has instructed us to remove a user who has objects, such as tables or stored procedures. However, these tables may be currently used elsewhere in the app or by views and stored procedures in the database.

Additionally, management may ask for data from tables in the Sales schema even though they instructed the DBA to delete the user who owns the object. And even worse, they may ask to us recreate the user after a few weeks because the user may have left the company as an employee but came back on as a consultant.

All of these scenarios point towards the DBA having to restore an old backup of the database and manually copying across the user accounts along with the schema and objects to the Production database. Depending on the retention policy of old backups, this option may not be available. If the account has objects and must be deleted, the DBA has 2 options.

Option 1

Change ownership of all the schemas owned by the barney_user account to another user. You can do this with the following script:

use company1;
go

declare @old_user nvarchar(100) = 'barney_user';
declare @new_user nvarchar(100) = 'fred_user';
declare @schema_name nvarchar(128);
declare @sql_command nvarchar(2000);

declare cur_schema cursor for 
	SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
	where schema_owner = @old_user;
begin
	OPEN cur_schema
	FETCH NEXT FROM cur_schema INTO @schema_name;

	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		  set @sql_command = 'ALTER AUTHORIZATION ON SCHEMA::' + @schema_name + ' TO ' + @new_user;
		  EXECUTE sp_executesql @sql_command;
		  FETCH NEXT FROM cur_schema INTO @schema_name;
	END 

	CLOSE cur_schema  
	DEALLOCATE cur_schema 
end;

Now you can drop the user account because it doesn’t own any schemas.

Option 2

Simply rename the user and remove the login access. Rename the user with something descriptive of the account, such as _deleted:

alter user barney_user with name = barney_deleted;

Next, remove all permissions from the user account. It’s much easier to do this using SSMS.
View the properties of the barney_deleted user and go to the Membership page. Uncheck all the user’s roles and click OK.

Finally, if the user is to be removed from all databases, disable the login and deny permission to connect to the database engine.

Now the user cannot login but you can still query tables in the Sales schema.