Jefferson Elias

How to drop a role in a SQL Server Database

September 6, 2017 by

Introduction

Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible.

In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations.

Common database role removal process

In this section, we will see how to drop a Database Role that does not have any members or any permissions assigned. Furthermore, it does not own any database object (schema, etc.).

How to drop a Database Role using SSMS?

Using SQL Server Management Studio (SSMS), we can drop a database role following these steps:

  1. Open SSMS and connect to the SQL Server instance
  2. Extend “Databases” node and the one corresponding to the database in which there is a role that has to be dropped.

  3. Continue to go down in tree view hierarchy by extending “Security” then “Roles” and “Database Roles” nodes.

  4. Right-click on the Database Role you want to delete and click on “Delete”.

  5. A dialog will appear and we just need to click on “OK” button.

And that’s it.

How to drop a Database Role using T-SQL

We just need to run following statement to drop a Database Role called [ToBeDropped].

Handling possible removal issues

Database roles are also database principals (like database users). This means that a database role can own objects. It can also be a member of one or more other database roles. In addition, database roles have permissions assigned to them and eventually role members that inherit those permissions.

To sum up, in real life, it’s not always just a matter of firing a DROP ROLE command. We can readily confirm that because even SSMS does not simply run this command when we use it!

In fact, if we generate the T-SQL statement that will drop the [ToBeDropped] database role, we may be surprised of the results.

To generate this statement, right click on the role to be dropped, then go over “Script Database Role as” then “Drop To” and select a destination for the generated script.

Here is the script that is generated using SSMS v17.1:

There are several noticeable facts that we can notice from this generated script:

  • It will check if the role is defined by Microsoft and we could expect a modified version of this script to fail whenever the database role is fixed (although there is no explicit RAISERROR instruction). Actually, here is the message we’d get if we try to drop [db_backupoperator] database role:

  • It will list out database principals that are a member of this role and remove their membership to the database role we want to drop.
  • Finally, it will drop the database role. This could be seen as a bug but it will generate an IF EXISTS argument even when the instance version is below 2016.

We can conclude that SSMS developers did think of database role removal as a complex task. But what if we want to drop a role that owns a database schema?

Let’s create this situation using following statements:

Now, let’s try to drop [RoleToBeDropped] database role.

Actually, it won’t work and we’ll get following error message:

So, there is more work to do and we’ll try to cover more cases that SSMS does by creating a new stored procedure that will manage the removal of a Database Role.

Actually, there other cases where a DROP ROLE statement will fail and some of them were covered for Database User removal in an article entitled “How to drop a SQL Server Login and all its dependencies“. The case includes:

  1. Role as Schema Owner
  2. Role as Permission grantor (revokes assigned permissions)
  3. Role as Role owner (for another database role in the same database)

In that article, we also concluded that we had to check for programmable database objects (procedure, function…) using the database user as execution context. We will ignore this case in our tests because, in SQL Server 2012 (and maybe in higher versions), a database role cannot be impersonated. To be convinced, we could give a try and run following statement that creates a stored procedure which should be executed as db_owner built-in database role.

After execution, we’ll get following error message:

In following, we’ll define a test case and scripts to build this test case like we did in the article mentioned above “How to drop a SQL Server Login and all its dependencies“. Then we’ll define a general stored procedure called DropDatabasePrincipal that will work for both Database Users and Database Roles (except we’ll focus on Database Roles for the purpose of this article).

Test case situation

Explanation

In this section, we will present the test case situation to which we will refer in the remaining of this article and where we want to drop a database role called RoleToBeDropped.

We are in a database called [TestDb].

  • The database role RoleToBeDropped owns:
    • A Schema called [ApplicationSchema1]
    • Another database role called [RoleA]
  • This role has following members:
    • A user called [ApplicationSQLUser1].
  • A procedure called [sp_ExecuteAsRole2BD] in [ApplicationSchema1] database schema:
    • references [ApplicationSQLUser1] database user
    • can be executed by a database user called [UserB] i.e. this user has GRANT EXECUTE permission on this procedure. This permission has been granted by [RoleToBeDropped] database role.
  • The Database User [dbo] has granted following permissions to RoleToBeDropped database role:
    • CONNECT WITH GRANT OPTION
    • ALTER ON DATABASE::[TestDb]

Diagram that depicts the situation

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

Creation script

You will find below, the necessary commands to create this situation in your environment. You can run the script multiple times it will work every time. This script can also be downloaded at the end of this article.

Building a stored procedure to drop database principals

Design of the interface

As there are common actions to perform in order to remove both database users and database roles, we will define a stored procedure that will handle both of them. We will call this procedure [DropDatabasePrincipal] and store it a database schema called [Administration].

Which parameters do we need for that procedure to do the task we want?

First, it needs a parameter to tell it which database is used. Let’s say @DatabaseName. By default, if no value is provided, the stored procedure will use the results of DB_NAME() built-in function.

Then, we also need to provide the name of the database principal that we want to delete. We will simply call this parameter @PrincipalName.

As we will have to check for permission assigned by the database principal, we could also add a conditional parameter that tells the procedure whether to reassign these permissions or not. We will call this parameter @PreserveAssignedPermissions. It would be of BIT data type with a default value of 1 as we don’t want to break what will remain.

If we want to keep assigned permissions, this means we need to reassign them using another database principal. You will find in “Appendix A – Assigning database permissions” some considerations that lead to the conclusion that we won’t define a @NewPermissionAssigner parameter and will simply use the default context.

In the same logic, we could want to reassign permissions this role provides to its members directly to them. Whether we do this or not would be defined by another parameter we would call @AssignRolePermissionsToItsMembers of BIT data type with a default value of 0. Again, we’ll use the default behavior of SQL Server to reassign these permissions when this parameter is set to 1.

Furthermore, we could tell the stored procedure whether to reassign database object ownership or not and if so, which database principal has to be used. For that purpose, we will define two additional parameters:

  1. @AlterDbObjectsOwnership, a Boolean value which set to 1 will tell the stored procedure to change the owner of database objects
  2. @NewDbObjectOwner that will be used only if former parameter is set to 1 and tell the stored procedure which database principal should be used to perform the object ownership reassignment

Finally, there are general parameters:

  • @WithLog that tells the stored procedure to whether log the call and its outcome into a logging table or not keeping any trace of that call
  • @Debug that, set to 1, will make the stored procedure more talkative.
  • @CheckOnly that, when set to 1, will make the stored procedure stop after all checks were passed and no action will be performed for actual principal removal. We’ll let this set to 1 by default in order to prevent from human mistakes.

Everything put together, this leads us to following interface for our stored procedure:

Procedure workflow

Here are the steps which should be in the stored procedure:

  1. Parameter validation:
    • Check if the database exists
    • Check if the principal exists
    • Check if the principal can be dropped
    • Consider @PreserveAssignedPermissions and @AssignRolePermissionsToItsMember. If either of these two parameters is set to 1, then check that database principal used as new permission assigner exists and is different from the principal that will be dropped.
    • If @AlterDbObjectsOwnership = 1 then check that database principal used as new object owner exists and is different from the database principal that will be dropped.
    • If the database principal is a user, check that it’s not set as the execution context for one or more procedures, functions (etc.)
  2. Get the list of permissions assigned by @PrincipalName into a temporary table called #AssignedPermissions.
    • If @PreserveAssignedPermissions is set to 0 and there are permissions in that list, then fail
  3. Get the list of database objects owned by @PrincipalName
    • Schemas
    • Roles
    If @AlterDbObjectsOwnership is set to 0 and there are objects in that list, then fail
  4. Get the list of roles for which @PrincipalName is a member.
  5. If database principal is a role:
    • get the list of its own members.
    • If @AssignRolePermissionsToItsMembers is set to 1, get the list of permissions directly assigned to @PrincipalName.
  6. If @CheckOnly parameter is set to 1, jump to step 8.
  7. Actual Role removal:
    • If necessary, assign database object membership to @NewDbObjectOwner.
    • If database principal is a role:
      • Remove all its members
      • If necessary, assign them permissions that were actually assigned to @PrincipalName.
    • Fire DROP ROLE command.
    • If necessary, assign back permissions previously assigned by @PrincipalName.
  8. Perform temporary tables cleanups

Implementation details

Getting back permissions assigned by a database principal

In order to get back permissions assigned by a principal name, we will create a temporary table called #AssignedPermissions and insert rows returned by a modified version of following query:

Getting back the list of database objects owned by a database principal

Here, we won’t cover every possible type of database object, but those presented in the test case i.e. database schemas and database roles. We will keep that information in a temporary table called #OwnedDbObjects which has following structure:

In order to get back database schemas, we’ll simply query sys.schemas table with a value for its principal_id column corresponding to the identifier for the database principal we want to drop.

Here is the query we’ll use:

In order to get back database roles owned by a database principal, we’ll just query sys.database_principals table with value for owning_principal_id corresponding to the identifier for the database principal we want to drop.

This can be performed using following query:

List database principal role memberships

We’ll just store this information in the same table as the above subsection, i.e. #OwnedDbObject with ‘MEMBERSHIP’ as the value for ObjectType column.

In order to list out database roles which has a database principal as a member, we’ll query sys.database_role_members system table where its column called member_principal_id has a value corresponding to the identifier for the database principal we want to drop.

Hence, we will use an adaptation of following query:

List members of a database role

We’ll just take a modified version of the query generated by SSMS for that part. This query will look in following system tables:

  • sys.database_principals
  • sys.database_role_members

Stored procedure body

You will find the script for creating the stored procedure in the Download section of this article.

Testing our stored procedure

You will find, attached at the end of the script, a series of tests that have been performed in order to check that a database role has been dropped as expected.

The first test consists of running the stored procedure with @AlterDbObjectsOwnership set to 0, meaning that it won’t set new owner for RoleA and ApplicationSchema1 database objects. This test should fail.

Here is the corresponding procedure call:

Next test consists in setting @PreserveAssignedPermissions parameter to 0 and @AlterDbObjectsOwnership back to its default value (1). This test should fail too as permissions are assigned and we don’t want to break everything just to drop a single database principal.

The T-SQL script to run this test is built around following code:

Another test will consist in setting the value of @PreserveAssignedPermissions parameter back to its default value (1) and running the stored procedure. This should actually work but, in cases where the database principal is a role, the permissions assigned to this role has not been transmitted to its members (before the execution of the stored procedure). This test is performed running following procedure call:

There is a last but not least test to perform where we want to transmit permissions from role to its former members, which we achieved with @AssignRolePermissionsToItsMembers parameter set to 1. Like the test just above, it should be successful.

Downloads

In the article entitled “How to drop a SQL Server Login and all its dependencies“, we already made a few tests and we know that SQL Server resets permissions on an object when ALTER AUTHORIZATION instruction is used. Here are some additional considerations that has to be taken in order to build the part of the DropDatabasePrincipal stored procedure that sets back permissions to the other database principals that should remain after it did its work.

First, let’s say [ApplicationSchema1] database schema is owned by [UserB] and we want to assign EXECUTE permission on a stored procedure in that schema to a role called [RoleA] as [dbo] database user. We would do this using following query:

Unfortunately, this statement will fail with following error message:

This is due to the fact that [dbo] users, even if it’s in db_owner database role does not have sufficient permissions to run this statement. In fact, in order to do this, we should first grant [dbo] the permission to execute the stored procedure and to share this permission:

But, this will also fail!

By the way, if there were a UserC database user that has the EXECUTE permission with GRANT OPTION on that object, following statement would work:


And we could assign permission to RoleA as UserC.

We can check permissions to RoleA and we’ll get:

At second, let’s notice that, by default, if we grant permissions on an object in a database schema, the database principal used to grant this permission is actually the one that owns the schema.

This means that we should operate as follows in order to set back permissions after a reassignment of schema ownership:

  1. List permissions assigned using the database principal we want to drop
  2. Fire the ALTER AUTHORIZATION statement for each schema
  3. Reassign collected permissions as new schema owner
Jefferson Elias
Latest posts by Jefferson Elias (see all)

Related posts:

  1. How to drop a SQL Server Login and all its dependencies
  2. Cómo bajar o eliminar el inicio de sesión de SQL Server y todas sus dependencias
  3. SQL Server 2017: SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback
  4. What is causing database slowdowns?
  5. Introducing schema documentation in SQL Server
Maintenance, 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