How to drop a SQL Server Login and all its dependencies

Introduction

Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example.

To do so, we can use SQL Server Management Studio (SSMS) as follows:

Open SSMS
Connect to a SQL Server instance
In Object Explorer, go to « Security » node then logins
Right-click on the SQL Server Login you want to drop then click on “Delete”
SSMS will show following warning message
Click on “OK”

We could also execute a DROP LOGIN statement:

DROP LOGIN login_name ;

No matter the path you followed, if everything went OK, your login is now dropped. But, as SSMS stated, you may have left a few things inside your databases. If there were mappings between this login and one or more database users (one per database), these users are still there.

We can come to that situation too when we restored one or more databases from a SQL Server instance (source server) to another server (destination server) where all logins mapped to these databases are not created on a destination server, or they are created but with different SID. Anyway, if you came to the situation you expected then it’s ok, but if not, it’s mandatory to carefully consider the advice in that warning that says we need to transfer ownership of schemas to another user.

But there are also cases where these steps will fail. For instance, if the login you want to drop is set as a database owner for one or more databases. It that case, you’ll get following screens in SSMS:

If you used T-SQL, you’ll get following error message:

In this article, we’ll try to drop a SQL Server Login and all its dependencies using T-SQL statements. This test and its conclusions will provide us relevant information that we can use if we are willing to automate this task or at least some parts of it.

In the following sections, we will first define and build a test case. Our objective will be to drop a SQL Server Login [ApplicationSQLUser1] and its corresponding databases users, both called [UserA]. We’ll first start trying to remove [UserA] database user from [Db1] database. Once we managed to perform this task, we’ll review the steps we followed and consider their appropriate sequencing. Once this is done, we’ll consider the removal of the login itself and also enlist the actions that have to be done.

Test case situation

Explanation

In this section, we will present the situation to which we will refer in this article where we want to drop a SQL Server login called [ApplicationSQLUser1], that is mapped to two SQL Server databases, [Db1] and [Db2]. This mapping is done between this login a database user called [UserA] in [Db1] and [dbo] in [Db2]. Actually, database [Db2] is owned by [ApplicationSQLUser1] login. There is also another login called [ApplicationSQLUser2] that is mapped to [UserB] in [Db1] database.

There are some other important facts:

At server level:
- [ApplicationSQLUser1] login is the owner of [Db3] database
- [ApplicationSQLUser1] login is granted CONNECT SQL permission and can grant it to any SQL Server Login.
- [ApplicationSQLUser1] login granted CONNECT SQL permission to [ApplicationSQLUser2] login
- On a SQL Server 2012+ instances, [ApplicationSQLUser1] login is the owner of a Server Role called [ServerRoleOwnedByUser1].

In [Db1] database:
- [UserA] is the owner a schema called [ApplicationSchema1]
- [UserA] is set as executor for a procedure called:
  
  1
  2
  3
  
  [ApplicationSchema1].[sp_ExecuteAsUserA].
- [UserB] has been granted the EXECUTE permission for that procedure by [UserA].
- [UserA] owns a database role called [RoleOwnedByUserA].

Diagram that depicts the situation

Here is a diagram that represents the situation described in previous subsection.

Creation script

You will find below necessary commands to create this situation in your environment. This code can be found in the script called “DropLogin-DropDbUser.tests.sql” attached to this article.

Create LOGINs

USE [master]

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ApplicationSQLUser1')

CREATE LOGIN [ApplicationSQLUser1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ApplicationSQLUser2')

CREATE LOGIN [ApplicationSQLUser2] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GRANT CONNECT SQL to ApplicationSQLUser1 WITH GRANT OPTION;

grant CONNECT SQL to ApplicationSQLUser2 AS ApplicationSQLUser1;

IF( (SELECT @@MICROSOFTVERSION / 0x01000000) >= 11 )

BEGIN

EXEC sp_executesql N'CREATE SERVER ROLE [ServerRoleOwnedByUser1] AUTHORIZATION [ApplicationSQLUser1];';

END;

Create DATABASES

CREATE DATABASE [Db1];

CREATE DATABASE [Db2];

Set MAPPINGS

create schemas

set schema ownership and default schemas

USE [Db1]

CREATE USER [UserA] FOR LOGIN [ApplicationSQLUser1]

CREATE USER [UserB] FOR LOGIN [ApplicationSQLUser2]

CREATE ROLE [RoleOwnedByUserA] AUTHORIZATION [UserA];

ALTER ROLE [db_ddladmin] ADD MEMBER [UserA]

CREATE SCHEMA ApplicationSchema1 AUTHORIZATION UserA;

ALTER USER [UserA] WITH DEFAULT_SCHEMA=[ApplicationSchema1]

ALTER USER [UserB] WITH DEFAULT_SCHEMA=[ApplicationSchema1]

USE master;

ALTER AUTHORIZATION ON DATABASE::Db2 TO ApplicationSQLUser1;

maps ApplicationSQLUser1 to [dbo] database user in Db2 database

Create stored procedure referencing UserA in Db1

Use Db1;

CREATE PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]

WITH EXECUTE AS 'UserA'

BEGIN

SELECT @@SERVERNAME

END;

grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA;

Dropping a database user

In this section, we’ll make different tests (based on test case situation) so that we can conclude on what we should do to make a good stored procedure that drops database users.

Note

These operations should be done only after a backup (full, differential or log)
This backup should be taken without any client connection except yours
If a problem occurs, you can restore this backup.

Manual drop of database user [UserA]

Let’s try to run following query and check what happens.

Use Db1 ;

DROP USER UserA ;

Here is our first error message that says we cannot drop this user because it’s the owner of one or more database schemas.

This is not a problem, let’s just run the following statement so that this message will disappear. Note that it’s pretty simple here because we have one schema.

USE Db1 ;

ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;

Note

Here we chose [dbo] database user but there is no obligation to use that one.

There is a change in permission grantor after this script has been executed. To be sure, just run following statement to set back [UserA] as schema owner:

USE Db1 ;

ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO UserA ;

grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA;

We have a permission set by [UserA] as we can see after running following query:

Use Db1;

select *

From sys.database_permissions

where grantor_principal_id = USER_ID('UserA')

We’ll get following results set:

If we run again the alter authorization on [ApplicationSchema1] to set it back to [dbo] database user:

USE Db1;

ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;

And run again previous query against sys. database_permissions, we get an empty result set:

Let’s check database permissions assigned to [UserB] database user with next query:

Use Db1;

select *

From sys.database_permissions

where grantee_principal_id = USER_ID('UserB')

We’ll get following results and we will see that the permission has simply been revoked.

This means that we potentially broke one or more client applications and we should store all permissions assigned by UserA before running this statement.

Let’s suppose everything went as expected and retry to drop database user:

Use Db1 ;

DROP USER UserA ;

Again, it failed because [UserA] owns [RoleOwnedByUserA] database role:

Let’s solve this problem by altering authorization to [dbo] database user on role [RoleOwnedByUserA]. This is achieved by running following statement:

Use Db1 ;

ALTER AUTHORIZATION ON ROLE::RoleOwnedByUserA TO [dbo] ;

Now, let’s try once more to drop database user. Guess what will happen after running DROP USER statement…

Use Db1 ;

DROP USER UserA ;

Actually, it did not succeed because we have a stored procedure that references this database user…

Here, we could have a problem because we could make an application that works at the moment completely fail. So, it’s recommended to test code modifications!

Here, we are sure of what we are doing, so we’ll modify the stored procedure so that it will execute as [dbo] database user.

Use Db1;

ALTER PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]

WITH EXECUTE AS 'dbo'

BEGIN

SELECT @@SERVERNAME

END;

Let’s try again to drop [UserA] database user. This time, it should succeed…

Use Db1 ;

DROP USER UserA ;

Indeed:

Let’s try one more thing and create once again our [UserA] database user. Let’s set this user as a member of a built-in database role, for instance [db_ddladmin]. To do so, we can run following T-SQL script:

USE Db1 ;

CREATE USER [UserA] FOR LOGIN [ApplicationSQLUser1]

ALTER ROLE [db_ddladmin] ADD MEMBER [UserA]

Now, let’s try to drop [UserA] and check if it ends successfully…

Use Db1 ;

DROP USER UserA ;

And it worked!

Operations performed altogether

Here is the complete script of the actions performed above.

USE Db1 ;

grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA;

ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;

ALTER AUTHORIZATION ON ROLE::RoleOwnedByUserA TO [dbo] ;

ALTER PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]

WITH EXECUTE AS 'dbo'

BEGIN

SELECT @@SERVERNAME

END;

DROP USER UserA ;

Conclusions from tests

In this section, we did native trials to drop a database user. We had to make decisions and changes to our database so that we were able to meet our goal. Now, it’s time to summarize what we’ve learned so that we would have everything defined to write a stored procedure that does all the actions we found mandatory to actually drop a database user.

First, we have to store permissions our database user granted to other users in a temporary table. We can do this with the following query already shown above.

Use Db1;

select *

From sys.database_permissions

where grantor_principal_id = USER_ID('UserA')

Why would we do that? Because an ALTER AUTHORIZATION statement on a schema will remove all permissions granted on the objects of that schema. This is expected behavior of that statement as we can see on Microsoft’s documentation page :

So, once we ran the ALTER AUTHORIZATION statement, we have to reassign these permissions.

Then, we should also consider role ownerships and we will have to reassign them to a user. Again, this is done by ALTER AUTHORIZATION and we should take care of what we do here, but if we already saved database permissions, then we have everything we need to set the permissions on roles back to what they were. To demonstrate this, let’s run following statement that will grant a permission on [RoleOwnedByUserA] database role:

USE Db1 ;

grant take ownership on ROLE::RoleOwnedByUserA to UserB

Let’s check permissions assigned to [UserB]…

Query:

Use Db1;

select *

From sys.database_permissions

where grantee_principal_id = USER_ID('UserB')

Results:

Finally, we had to review existing code so that we can’t find any reference to [UserA] in the body of procedural objects (stored procedures, functions, etc).

However, there is one more thing we can conclude. It’s that there is an order to respect if we want to automate the removal of a database user U:

Check for references to database user U inside procedure objects. If any is found, then raise an error.
Take appropriate action between following ones:
1. If an error occurred in step 1, review code then retry step 1
2. If no error occurred, go to step 3.
Now we can store database permissions assigned by database user U into a temporary table.
List all database schemas owned by database user U and set a new owner for them.
List all database roles owned by database user U and set a new owner for them.
Drop database user U.

Dropping a SQL Server Login

Dropping all database users mapped to a login

In our test situation, there were two database mappings defined for [ApplicationSQLUser1] login. We already managed to drop the most difficult one, [UserA] in [Db1] database. As we need to remove any reference in any mapped database, it’s time to consider the second one.

Actually, this mapping is done to [dbo] database user, which is a reserved user that we can’t drop. It’s a consequence of the fact that [ApplicationSQLUser1] login owns. We’ll review this in a moment and it won’t be a problem anymore.

However, if we were in front of another user than [dbo], we should follow the exact same path as we did for [UserA] user in [Db1] database.

Manual removal of SQL Server Login [ApplicationSQLUser1]

Let’s try to drop this login directly by running following statement:

USE master;

DROP LOGIN [ApplicationSQLUser1];

Unfortunately, this statement will fail with following error:

This means that we should take a copy of permissions granted by this login and revoke them and grant them again as another SQL Server login like [sa]. We can list existing server permissions using the following query:

select *

from master.sys.server_permissions

where grantor_principal_id = SUSER_ID('ApplicationSQLUser1');

In this test case, we will get following results:

We can solve this by granting the CONNECT SQL permission as [sa] as follows:

USE master ;

REVOKE CONNECT SQL FROM ApplicationSQLUser2 AS ApplicationSQLUser1;

GRANT CONNECT SQL TO ApplicationSQLUser2 AS SA;

If we run again the query to get back server level permissions, we’ll get an empty result set:

Now, we are ready for another trial to remove [ApplicationSQLUser1] login…

USE master;

DROP LOGIN [ApplicationSQLUser1];

Here, we get another error message stating that we need to review server role ownerships:

Note

You won’t get this error message on a SQL Server 2008 R2 or lower version of SQL Server as, in these versions, we can’t create a custom server role.

We need to get the list of server roles owned by [ApplicationSQLUser1] login. There is the query that will help us for that:

select *

From master.sys.server_principals

WHERE owning_principal_id = SUSER_ID('ApplicationSQLUser1')

And here is the result set for this query:

We need to, once more, use ALTER AUTHORIZATION statement to get rid of this error message:

USE [master]

ALTER AUTHORIZATION ON SERVER ROLE::[ServerRoleOwnedByUser1] TO [sa] ;

Now, let’s try once again to drop our [ApplicationSQLUser1] login…

USE master;

DROP LOGIN [ApplicationSQLUser1];

We’ll get following error message:

To get rid of this error, we need to list out databases which owner is [ApplicationSQLUser1] login. We’ll do this using following statement:

select *

FROM sys.databases

where owner_sid = SUSER_SID('ApplicationSQLUser1');

Results for that query will show us that [Db2] database is owned by this login.

We can change database owner to [sa] using following statement:

ALTER AUTHORIZATION ON DATABASE::Db2 TO sa;

Note

Pay close attention to this statement because it actually remaps [dbo] database user to the server login provided after reserved word « TO », which is [sa] in our case.

Once we’ve run the previous statement, we will be able to finally drop the login!

Operations performed altogether

Here are the operations we performed in last subsection.

USE master ;

REVOKE CONNECT SQL FROM ApplicationSQLUser2 AS ApplicationSQLUser1;

GRANT CONNECT SQL TO ApplicationSQLUser2 AS SA;

ALTER AUTHORIZATION ON SERVER ROLE::[ServerRoleOwnedByUser1] TO [sa] ;

ALTER AUTHORIZATION ON DATABASE::Db2 TO sa;

DROP LOGIN [ApplicationSQLUser1];

Conclusion from tests

As we’ve seen for database users, dropping a SQL Server Login is not always a simple task. There are multiple operations to do.

Let’s sum them up:

(Optional) If your SQL Server instance is hosted on a virtual machine, shutdown this machine, take a snapshot and restart. You could also take a full disk backup with SQL Server down.
Check for mappings between the SQL Server Login and one or more databases of our SQL Server Instance
For each database mapping found, drop corresponding database user following steps described above.

Note

This operation may fail and we could add an additional step that would check in each database if there are usages of references to database users mapped with the login we want to drop. If any reference were found, then login removal would fail.
Take a copy of existing server permissions granted by the login we want to drop
Revoke these permissions and reassign them as a different server principal
Review server roles owned by the login we want to drop and assign them a different owner
Review databases owned by the login we want to drop and assign them a different owner
(Review other kinds of server objects that were not considered during our tests and take appropriate actions)
Drop the login

Cleanups

Now we finished our tests, we can cleanup what we’ve created. We can run following script to do that:

USE master;

DROP DATABASE [Db1];

DROP DATABASE [Db2];

Already dropped (this is the purpose of this article)

DROP LOGIN [ApplicationSQLUser1];

DROP LOGIN [ApplicationSQLUser2];

IF( (SELECT @@MICROSOFTVERSION / 0x01000000) >= 11 )

BEGIN

EXEC sp_executesql N'DROP SERVER ROLE [ServerRoleOwnedByUser1];';

END;

Conclusion

As we’ve seen along this article, it’s not that simple to correctly drop a SQL Server login. There are many steps to follow and some of these are not that simple: we might have to review procedural code and permissions in order to just do what we want to d

Nevertheless, we identified the actions to take for login and database user removal and we could conclude that these actions could be (partially) scripted or put in one or more stored procedure.

Downloads

DropLogin-DropDbUser.tests.sql

Author
Recent Posts

Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Latest posts by Jefferson Elias (see all)

How to perform a performance test against a SQL Server instance - September 14, 2018
Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
How to link two SQL Server instances with Kerberos - July 5, 2018

Introduction

Test case situation

Dropping a database user

Dropping a SQL Server Login

Cleanups

Conclusion

Downloads

Related posts: