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:

http://technet.microsoft.com/en-us/library/ms188659(SQL.100).aspx

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

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.