{"id":3137,"date":"2022-04-09T16:06:55","date_gmt":"2022-04-09T09:06:55","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3137"},"modified":"2022-04-09T17:25:40","modified_gmt":"2022-04-09T10:25:40","slug":"sql-server-drop-role","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-administration\/sql-server-drop-role\/","title":{"rendered":"SQL Server DROP ROLE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL Server DROP ROLE<\/code> statement to remove a role from the current database.<\/p>\n\n\n\n

Introduction to the SQL Server DROP ROLE statement #<\/a><\/h2>\n\n\n\n

The DROP ROLE<\/code> statement removes a role<\/a> from the current database. Here’s the syntax of the DROP ROLE<\/code> statement:<\/p>\n\n\n

DROP<\/span> ROLE<\/span> [IF<\/span> EXISTS<\/span>] role_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the role that you want to remove after the DROP ROLE<\/code> keywords.<\/p>\n\n\n\n
The IF EXISTS<\/code> clause is optional and has been available since SQL Server 2016. The IF EXISTS<\/code> conditionally removes the role only if it already exists.<\/p>\n\n\n\n
The DROP ROLE<\/code> 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.<\/p>\n\n\n\n
The DROP ROLE<\/code> 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.<\/p>\n\n\n\n
The DROP ROLE<\/code> statement cannot remove fixed database roles like db_datareader<\/code>, db_datawriter<\/code>, db_securityadmin<\/code>, etc.<\/p>\n\n\n\n
SQL Server DROP ROLE statement example #<\/a><\/h2>\n\n\n\n
The following example uses the BikeStores<\/code> sample database<\/a>. We’ll use the sales<\/code> and sox_auditors<\/code> roles created in the CREATE ROLE<\/code> tutorial<\/a>.<\/p>\n\n\n\n
1) Using the SQL Server DROP ROLE statement to drop a role #<\/a><\/h3>\n\n\n\n
The following example uses the DROP ROLE<\/code> statement to drop the sox_auditors role from the BikeStores<\/code> database:<\/p>\n\n\n
DROP<\/span> ROLE<\/span> IF<\/span> EXISTS<\/span> sox_auditors;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the role sox_auditors<\/code> has no member, the statement executes successfully.<\/p>\n\n\n\n
2) Remove a role that has members example #<\/a><\/h3>\n\n\n\n
First, use the DROP ROLE<\/code> statement to remove the role sales<\/code> from the database:<\/p>\n\n\n
DROP<\/span> ROLE<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the role sales<\/code> has members, SQL Server issues the following error:<\/p>\n\n\n
The role has members. It must be empty before it can be dropped.<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, to find the members that belong to the role sales<\/code>, you use the following statement:<\/p>\n\n\n
SELECT<\/span>\n  r.name role_name,\n  r.type role_type,\n  r.type_desc role_type_desc,\n  m.name member_name,\n  m.type member_type,\n  m.type_desc member_type_desc\nFROM<\/span> sys.database_principals r\nINNER<\/span> JOIN<\/span>  sys.database_role_members  rm on<\/span> rm.role_principal_id = r.principal_id\nINNER<\/span> JOIN<\/span> sys.database_principals m on<\/span> m.principal_id = rm.member_principal_id\nWHERE<\/span> r.name ='sales'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The output shows that the role sales<\/code> has one member which is the user james<\/code>.<\/p>\n\n\n\n
Third, remove the user james<\/code> from the role sales<\/code> using the ALTER ROLE... DROP MEMBER<\/code> statement:<\/p>\n\n\n
ALTER<\/span> ROLE<\/span> sales\nDROP<\/span> MEMBER<\/span> james;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, remove the sales roles using the DROP ROLE<\/code> statement:<\/p>\n\n\n
DROP<\/span> ROLE<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • Use the DROP ROLE<\/code> statement to remove a role from the current database.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t