SQL Server Security

Leave a comment (1) Go to comments

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 Security What Are Securables? SQL Server Security Framework sql server security SQL Server Principals SQL Server Permissions Server Level Roles Securables Securable scope: Schema Securable scope: Database Securable scope Permissions Hierarchy Objects Kerberos with SQL Server Database Level Roles

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.
EOF - SQL Server Security, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Thank you for the clear and simple explanation. I was searching for such everywhere and finally found. Thanks again. Best regards, Anatoli

    P.S. You are bookmarked.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.