SQL Server Security

SQL Server manages security through a series of hierarchical entities called securables and users or user groups know as principals. SQL Server regulates the actions of principals on securables by verifying that they have been granted appropriate permissions. Understanding how this hierarchy works is important to proper implementation of your security strategy.
This lesson provides an introduction to the fundamental components of SQL Server security: principals and securables. It also describes the permissions you can apply to securables within the SQL Server security model.


The Database Engine manages a hierarchical collection of entities that can be secured with permissions.
Kerberos with SQL Server
SQL Server supports Kerberos indirectly through the Windows Security Support Provider Interface (SSPI) when SQL Server is using Windows authentication. SSPI allows an application to use various security models available on a computer or network without changing the interface to the security system.
SQL Server allows SSPI to negotiate the authentication protocol to use; if Kerberos cannot be used, Windows will fall back to Windows NT Challenge/Response (NTLM) authentication.
SQL Server 2008 supports Kerberos authentication on the following protocols:

• TCP/IP
• Named pipes
• Shared memory

Permissions Hierarchy
The Database Engine manages a hierarchical collection of entities that can be secured with permissions. These entities are known as securables. The most prominent securables are servers and databases, but discrete permissions can be set at a much finer level. SQL Server regulates the actions of principals on securables by verifying that they have been granted appropriate permissions.
Permissions can be manipulated with the familiar Transact-SQL queries GRANT, DENY, and REVOKE. Information about permissions is visible in the sys.server_permissions and sys.database_permissions catalog views. There is also support for querying permissions information by using built-in functions.
What are Principals?
Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

SQL Server Principals

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

The SQL Server sa Login
The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.
public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
INFORMATION_SCHEMA and sys
Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and they cannot be modified or dropped.
Certificate-based SQL Server Logins
Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate## Client and Database Server

 By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.
What Are Securables ?
Securables are the resources to which the SQL Server Database Engine authorization system regulates access. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured.
Securables are the entities in SQL Server that permissions can be granted on. This means that principals (for example, users, logins) obtain permission to securables. There is a hierarchy within each scope. Servers contain databases, databases contain schemas, and schemas contain objects. When certain permissions are granted on a securable at the server level, implied permission is granted at the database and schema levels. The securable scopes are server, database, and schema.
Securable scope: Server
Contains the following securables:

• Endpoint
• Login
• Database

Securable scope: Database
Contains the following securables:

• User
• Role
• Application role
• Assembly
• Message Type
• Route
• Service
• Remote Service Binding
• Fulltext Catalog
• Certificate
• Asymmetric Key
• Symmetric Key
• Contract
• Schema

Securable scope: Schema
Contains the following securables:

• Type
• XML Schema Collection
• Object

Objects
The following are members of the object class:

• Aggregate
• Constraint
• Function
• Procedure
• Queue
• Statistic
• Synonym
• Table
• View

SQL Server Permissions
Server-Level Roles
To easily manage the permissions on your server, SQL Server provides several roles, which are security principals that group other principals. Roles are like groups in the Microsoft Windows operating system.
Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope.
You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role.
The following table shows the server-level roles and their capabilities.

Server-Level Role Name	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role.
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.

Working with Server-Level Roles
The following table explains the commands, views and functions for working with server-level roles.

Feature	Type	Description
sp_helpsrvrole (Transact-SQL)	Metadata	Returns a list of server-level roles.
sp_helpsrvrolemember (Transact-SQL)	Metadata	Returns information about the members of a server-level role.
sp_srvrolepermission (Transact-SQL)	Metadata	Displays the permissions of a server-level role.
IS_SRVROLEMEMBER (Transact-SQL)	Metadata	Indicates whether a SQL Server login is a member of the specified server-level role.
sys.server_role_members (Transact-SQL)	Metadata	Returns one row for each member of each server-level role.
sp_addsrvrolemember (Transact-SQL)	Command	Adds a login as a member of a server-level role.
sp_dropsrvrolemember (Transact-SQL)	Command	Removes a SQL Server login or a Windows user or group from a server-level role.

Database-Level Roles
To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in the msdb database.

You can add any database account and other SQL Server roles into database-level roles. Each member of a fixed database role can add other logins to that same role.
The following table shows the fixed database-level roles and their capabilities. These roles exist in all databases.

Database-level role name	Description
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.