DROP SERVER ROLE (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Removes a user-defined server role.
User-defined server roles are new in SQL Server 2012.
Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Data Warehouse Public Preview.
User-defined server roles that own securables cannot be dropped from the server. To drop a user-defined server role that owns securables, you must first transfer ownership of those securables or delete them.
User-defined server roles that have members cannot be dropped. To drop a user-defined server role that has members, you must first remove members of the role by using ALTER SERVER ROLE.
Fixed server roles cannot be removed.
You can view information about role membership by querying the sys.server_role_members catalog view.
A. To drop a server role
The following example drops the server role purchasing.
DROP SERVER ROLE purchasing; GO
B. To view role membership
To view role membership, use the Server Role (Members) page in SQL Server Management Studio or execute the following query:
SELECT SRM.role_principal_id, SP.name AS Role_Name, SRM.member_principal_id, SP2.name AS Member_Name FROM sys.server_role_members AS SRM JOIN sys.server_principals AS SP ON SRM.Role_principal_id = SP.principal_id JOIN sys.server_principals AS SP2 ON SRM.member_principal_id = SP2.principal_id ORDER BY SP.name, SP2.name
C. To view role membership
To determine whether a server role owns another server role, execute the following query:
SELECT SP1.name AS RoleOwner, SP2.name AS Server_Role FROM sys.server_principals AS SP1 JOIN sys.server_principals AS SP2 ON SP1.principal_id = SP2.owning_principal_id ORDER BY SP1.name ;