SQL Server DROP ROLE

Summary: in this tutorial, you’ll learn how to use the SQL Server DROP ROLE statement to remove a role from the current database.

Introduction to the SQL Server DROP ROLE statement

The DROP ROLE statement removes a role from the current database. Here’s the syntax of the DROP ROLE statement:

DROP ROLE [IF EXISTS] role_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the role that you want to remove after the DROP ROLE keywords.

The IF EXISTS clause is optional and has been available since SQL Server 2016. The IF EXISTS conditionally removes the role only if it already exists.

The DROP ROLE statement cannot remove a role that owns securables. To drop a role that owns the securables, you need to first transfer ownership of those securables and then drop the role from the database.

The DROP ROLE statement cannot remove a role that has members. To remove a role that has members, you need to remove the members from the role before dropping it.

The DROP ROLE statement cannot remove fixed database roles like db_datareader, db_datawriter, db_securityadmin, etc.

SQL Server DROP ROLE statement example

The following example uses the BikeStores sample database. We’ll use the sales and sox_auditors roles created in the CREATE ROLE tutorial.

1) Using the SQL Server DROP ROLE statement to drop a role

The following example uses the DROP ROLE statement to drop the sox_auditors role from the BikeStores database:

DROP ROLE IF EXISTS sox_auditors;Code language: SQL (Structured Query Language) (sql)

Since the role sox_auditors has no member, the statement executes successfully.

2) Remove a role that has members example

First, use the DROP ROLE statement to remove the role sales from the database:

DROP ROLE sales;Code language: SQL (Structured Query Language) (sql)

Since the role sales has members, SQL Server issues the following error:

The role has members. It must be empty before it can be dropped.Code language: plaintext (plaintext)

Second, to find the members that belong to the role sales, you use the following statement:

SELECT
  r.name role_name,
  r.type role_type,
  r.type_desc role_type_desc,
  m.name member_name,
  m.type member_type,
  m.type_desc member_type_desc
FROM sys.database_principals r
INNER JOIN  sys.database_role_members  rm on rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m on m.principal_id = rm.member_principal_id
WHERE r.name ='sales';Code language: SQL (Structured Query Language) (sql)

Output:

The output shows that the role sales has one member which is the user james.

Third, remove the user james from the role sales using the ALTER ROLE... DROP MEMBER statement:

ALTER ROLE sales
DROP MEMBER james;Code language: SQL (Structured Query Language) (sql)

Finally, remove the sales roles using the DROP ROLE statement:

DROP ROLE sales;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the DROP ROLE statement to remove a role from the current database.