Fixed Server role for SQL Server 2008

The following is description of fixed server roles that exists in SQL Server.

• Public

o Any login created in SQL Server must be a member of public role.

• sysadmin

o If login is a member of sysadmin, then login can do anything in the SQL Server even if this login blocked from doing something.

o If you need to check login which is a member of specific role, type the following statement in a new SQL Server query

SELECT IS_SRVROLEMEMBER('sysadmin');

• bulkadmin

o transferring data from out of SQL Server to inside it (bulk insert operation) or other operation such as service accounts that are performing Extract, Transform, and Load (ETL) need to be performed by a login that a member of bulkadmin.

o To be a member of bulkadmin not enough to do bulk insert operation for example but it’s need also to has insert permission to complete operation successfully.

• dbcreator

o This role allows to its members the ability of creating databases.

• diskadmin

o This role allows to its members the ability of managing backup devices.

• processadmin

o This role allows to its members the ability of alter any connections.

• securityadmin

o This role allows to its members the ability of managing and controlling logins properties for SQL Server.

o The member of securityadmin role can manage other server roles but not sysadmin role.

o This role can’t define any internal security within a database.

• serveradmin

o This role to manage the SQL Server configuration.

o The Member of this role can shutdown SQL Server .

 Shutdown SQL Server by command SHUTDOWN or SHUTDOWN WITH NOWAIT

 To display a list of all active processes and locks, run exec sp_who and exec sp_lock.

• setupadmin

o This role allows to its members the ability of controlling linked servers.