Key SQL Server Security Concepts and Pre-Defined Roles

Miscellaneous key thoughts on security concepts and pre-defined roles and permissions:

PRE-BUILT SERVER LEVEL SECURITY ROLES

*  See this link for list of pre-built server-level roles:

sysadmin 
Members of the sysadmin fixed server role can perform any activity in the server. 

serveradmin 
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server. 

securityadmin 
Members 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. Additionally, they can reset passwords for SQL Server logins.
 
processadmin 
Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server. 

setupadmin
Members of the setupadmin fixed server role can add and remove linked servers. 

bulkadmin 
Members of the bulkadmin fixed server role can run the BULK INSERT statement. 

diskadmin
The diskadmin fixed server role is used for managing disk files. 

dbcreator 
Members of the dbcreator fixed server role can create, alter, drop, and restore any database. 

public 
Every 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.  


PRE-BUILT DATABASE LEVEL SECURITY ROLES

*  See this link for list of pre-built database-level roles:   http://msdn.microsoft.com/en-us/library/ms189121.aspx

db_owner

Members of the db_owner fixed database role can perform 

all configuration and maintenance 

activities on the database, and can also drop the database.

db_securityadmin

Members of the db_securityadmin fixed database role can modify

 role membership and 

manage permissions. Adding principals to this role could enable 

unintended privilege escalation.

db_accessadmin

Members of the db_accessadmin fixed database role can 

add or remove access

 to the database for Windows logins, Windows groups, and

 SQL Server logins.

db_backupoperator

Members of the db_backupoperator fixed database role can 

back up the database.

db_ddladmin

Members of the db_ddladmin fixed database role can run any 

Data Definition Language (DDL) command in a database.

db_datawriter

Members of the db_datawriter fixed database role can add, delete, 

or change data in all user tables.

db_datareader

Members of the db_datareader fixed database role can read 

all data from all user tables.

db_denydatawriter

Members of the db_denydatawriter fixed database role cannot 

add, modify, 

or delete any data in the user tables within a database.

db_denydatareader

Members of the db_denydatareader fixed database role cannot 

read any data in the user tables within a database.



MISCELLANEOUS OTHER SECURITY COMMENTS


*  Trustworthy setting

The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement. For example, ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;.   Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy.

*  To create a maintenance plan you must be a member of the "sysadmin" role

*  To be able to run a SQL Profiler Trace, you have to execute a "ALTER TRACE"  command.   The pre-built fixed server roles mentioned above do not give someone sufficient security (except sysadmin).

*  There is no pre-defined database fixed role that let's a user execute all stored procedures in a database.