drop-server-role-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
DROP SERVER ROLE (Transact-SQL)	DROP SERVER ROLE (Transact-SQL)	VanMSFT	vanto	03/06/2017	sql	t-sql	reference	DROP SERVER ROLE DROP_SERVER_ROLE_TSQL	SERVER ROLE, DROP DROP SERVER ROLE statement	TSQL	>=aps-pdw-2016||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

DROP SERVER ROLE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2012-xxxx-xxxx-pdw-md]

Removes a user-defined server role.

User-defined server roles are new in [!INCLUDEssSQL11].

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DROP SERVER ROLE role_name  

Arguments

role_name
Specifies the user-defined server role to be dropped from the server.

Remarks

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.

Permissions

Requires CONTROL permission on the server role or ALTER ANY SERVER ROLE permission.

Examples

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 [!INCLUDEssManStudioFull] 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 ;  

See Also

ALTER ROLE (Transact-SQL)
CREATE ROLE (Transact-SQL)
Principals (Database Engine)
DROP ROLE (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_addrolemember (Transact-SQL)
sys.database_role_members (Transact-SQL)
sys.database_principals (Transact-SQL)