Jefferson Elias

How to drop a SQL Server Login and all its dependencies

July 19, 2017 by

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:

  1. Open SSMS
  2. Connect to a SQL Server instance
  3. In Object Explorer, go to « Security » node then logins

  4. Right-click on the SQL Server Login you want to drop then click on “Delete”

  5. SSMS will show following warning message

  6. Click on “OK”

We could also execute a DROP LOGIN statement:

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:
    • [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.

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.

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.

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 ;

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

    We’ll get following results set:

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

    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:

    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:

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:

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

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.

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

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:

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

And it worked!

Operations performed altogether

Here is the complete script of the actions performed above.

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.

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:

Let’s check permissions assigned to [UserB]

Query:

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:

  1. Check for references to database user U inside procedure objects. If any is found, then raise an error.
  2. 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.
  3. Now we can store database permissions assigned by database user U into a temporary table.
  4. List all database schemas owned by database user U and set a new owner for them.
  5. List all database roles owned by database user U and set a new owner for them.
  6. 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:

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:

In this test case, we will get following results:

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

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…

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:

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:

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

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:

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:

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.

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:

  1. (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.
  2. Check for mappings between the SQL Server Login and one or more databases of our SQL Server Instance
  3. 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.

  4. Take a copy of existing server permissions granted by the login we want to drop
  5. Revoke these permissions and reassign them as a different server principal
  6. Review server roles owned by the login we want to drop and assign them a different owner
  7. Review databases owned by the login we want to drop and assign them a different owner
  8. (Review other kinds of server objects that were not considered during our tests and take appropriate actions)
  9. Drop the login

Cleanups

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

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

Jefferson Elias
Maintenance, Relationships and dependencies, Security

About 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

168 Views