Server Level Roles

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. SQL Server provides nine fixed server roles.

Fixed server roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.

NOTE: The permissions that are granted to the fixed server roles (except public) cannot be changed.

Server-Level Roles

The following table shows the fixed server-level roles and their capabilities.

Fixed server-level roleDescription
sysadminMembers of the sysadmin fixed server role can perform any activity in the server.
serveradminMembers of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadminMembers of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
processadminMembers of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadminMembers of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements.
bulkadminMembers of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadminThe diskadmin fixed server role is used for managing disk files.
dbcreatorMembers of the dbcreator fixed server role can create, alter, drop, and restore any database.
publicEvery SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.
server level role

The following table explains the commands, views, and functions that you can use to work with server-level roles.

FeatureTypeDescription
sp_helpsrvroleMetadataReturns a list of server-level roles.
sp_helpsrvrolememberMetadataReturns information about the members of a server-level role.
sp_srvrolepermissionMetadataDisplays the permissions of a server-level role.
IS_SRVROLEMEMBERMetadataIndicates whether a SQL Server login is a member of the specified server-level role.
sys.server_role_membersMetadataReturns one row for each member of each server-level role.
sp_addsrvrolememberCommandAdds a login as a member of a server-level role. Deprecated. Use ALTER SERVER ROLE instead.
sp_dropsrvrolememberCommandRemoves a SQL Server login or a Windows user or group from a server-level role. Deprecated. Use ALTER SERVER ROLE instead.
CREATE SERVER ROLECommandCreates a user-defined server role.
ALTER SERVER ROLECommandChanges the membership of a server role or changes name of a user-defined server role.
DROP SERVER ROLECommandRemoves a user-defined server role.
IS_SRVROLEMEMBERFunctionDetermines membership of server role.

sp_helpsrvrole

Returns a list of the SQL Server fixed server roles.

Syntax:

sp_helpsrvrole [ [ @srvrolename = ] 'role' ]

Example:
The following query returns the list of fixed server roles.

EXEC sp_helpsrvrole ;

sp_helpsrvrolemember

Returns information about the members of a SQL Server fixed server role.

Syntax:

sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]

Example:
The following example lists the members of the sysadmin fixed server role.

EXEC sp_helpsrvrolemember 'sysadmin';

sp_srvrolepermission

Displays the permissions of a fixed server role.

Syntax:

sp_srvrolepermission [ [ @srvrolename = ] 'role']

Example:
The following query returns the permissions associated with the sysadmin fixed server role.

EXEC sp_srvrolepermission 'sysadmin';
GO

IS_SRVROLEMEMBER

Indicates whether a SQL Server login is a member of the specified server role.

Syntax:

IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

Example:
The following example indicates whether the SQL Server login for the current user is a member of the sysadmin fixed server role.

IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   print 'Current user''s login is NOT a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL  
   print 'ERROR: The server role specified is not valid.';  

sys.server_role_members

Returns one row for each member of each fixed and user-defined server role.

Example:
The following example returns the names and id’s of the roles and their members.

SELECT sys.server_role_members.role_principal_id, role.name AS RoleName,   
    sys.server_role_members.member_principal_id, member.name AS MemberName  
FROM sys.server_role_members  
JOIN sys.server_principals AS role  
    ON sys.server_role_members.role_principal_id = role.principal_id  
JOIN sys.server_principals AS member  
    ON sys.server_role_members.member_principal_id = member.principal_id; 

sp_addsrvrolemember

Adds a login as a member of a fixed server role.

Syntax:

sp_addsrvrolemember [ @loginame= ] 'login' , [ @rolename = ] 'role'

Example:
The following example adds the Windows login Corporate\HelenS to the sysadmin fixed server role.

EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin';  
GO

sp_dropsrvrolemember

Removes a SQL Server login or a Windows user or group from a fixed server role.

Syntax:

sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role'

Example:
The following example removes the login JackO from the sysadmin fixed server role.

EXEC sp_dropsrvrolemember 'JackO', 'sysadmin';

CREATE SERVER ROLE

Creates a new user-defined server role.

Syntax:

CREATE SERVER ROLE role_name [ AUTHORIZATION server_principal ]

Example:
Creating a server role that is owned by a login
The following example creates the server role buyers that is owned by login BenMiller.

USE master;  
CREATE SERVER ROLE buyers AUTHORIZATION BenMiller;  
GO

ALTER SERVER ROLE

Changes the membership of a server role or changes name of a user-defined server role. Fixed server roles cannot be renamed.

Syntax:

ALTER SERVER ROLE server_role_name   
{  
    [ ADD MEMBER server_principal ]  
  | [ DROP MEMBER server_principal ]  
  | [ WITH NAME = new_server_role_name ]  
} [ ; ]

Example:
Adding a domain account to a server role
The following example adds a domain account named adventure-works\roberto0 to the user-defined server role named Production.

ALTER SERVER ROLE Production ADD MEMBER [adventure-works\roberto0] ; 

Adding a SQL Server login to a server role
The following example adds a SQL Server login named Ted to the diskadmin fixed server role.

ALTER SERVER ROLE diskadmin ADD MEMBER Ted ;  
GO

DROP SERVER ROLE

Removes a user-defined server role.

Syntax:

DROP SERVER ROLE role_name

Example:
To drop a server role
The following example drops the server role purchasing.

DROP SERVER ROLE purchasing;  
GO

IS_SRVROLEMEMBER

Indicates whether a SQL Server login is a member of the specified server role.

Syntax:

IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

Example:
The following example indicates whether the SQL Server login for the current user is a member of the sysadmin fixed server role.

IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   print 'Current user''s login is NOT a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL  
   print 'ERROR: The server role specified is not valid.';  

The following example indicates whether the domain login Pat is a member of the diskadmin fixed server role.

SELECT IS_SRVROLEMEMBER('diskadmin', 'Contoso\Pat'); 

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply