Security

Create administrator

Fresh installed database has no security. To secure it, first, create an administrator account:

CREATE ADMIN [myadmin] WITH PASSWORD [mypwd]

The command will create administrator "myadmin". The administrator inherits all privileges. If you use a manager, create a new connection in servers’ explorer tree with specifying username/password.

Manage users & roles

Create user

To create a user, execute SQL:

CREATE USER [myuser] WITH PASSWORD [mypassword];

or, windows user (for windows integrated security):

CREATE USER [mydomain\myuser]

User is transparent to all databases. There can be only one user with the distinct name.

The executing user must have [CREATE USER] system permission.

Drop user

To drop a user, execute SQL:

DROP USER [myuser]

The executing user must have [DROP USER] system permission.

Create roles

The user created database role sets the database object privileges that can be passed to a group of users. To create a role SQL:

USE [mydb];
GO;
CREATE ROLE [myrole];

The roles are defined per database. Therefore, either execute "USE.." command to set the roles' database or specify database in roles name:

CREATE ROLE [mydb.myrole];

The executing user must have [CREATE ROLE] database permission.

Drop roles

USE [mydb];
GO;
DROP ROLE [myrole];

or

DROP ROLE [mydb.myrole];

The executing user must have [DROP ROLE] database permission.

Add or remove role membership

To change roles'/users' membership, the logged user must have [ALTER MEMBERSHIP] database permission. To add role to another role, execute:

USE [mydb];
GO;
ADD ROLE [myrole] TO ROLE [myOtherRole];

or

ADD ROLE [mydb.myrole] TO ROLE [mydb.myOtherRole];

After execution, [myOtherRole] will inherit roles' [myrole] privileges.

To add role to user, execute:

USE [mydb];
GO;
ADD ROLE [myrole] TO USER [myUser];

To remove role membership from role, execute:

USE [mydb];
GO;
REMOVE ROLE [myrole] FROM ROLE [myOtherRole]

To remove role membership from user, execute:

USE [mydb];
GO;
REMOVE ROLE [myrole] FROM USER [myUser]

Grant privileges

The grant privilege can be per database level, i.e. for all database objects. For example, following Sql will grant to role [myRole] SELECT/UPDATE/INSERT/DELETE rights on all tables and EXECUTE to all procedures:

USE mydb;
GO;
GRANT SELECT  TO ROLE [myRole] WITH GRANT OPTION
GRANT UPDATE  TO ROLE [myRole] WITH GRANT OPTION
GRANT INSERT  TO ROLE [myRole] WITH GRANT OPTION
GRANT DELETE  TO ROLE [myRole] WITH GRANT OPTION
GRANT EXECUTE TO ROLE [myRole] WITH GRANT OPTION
GRANT ALTER MEMBERSHIP TO ROLE [myRole] WITH GRANT OPTION

For user grant:

USE mydb;
GO;
GRANT SELECT  TO USER [myUser] WITH GRANT OPTION
GRANT UPDATE  TO USER [myUser] WITH GRANT OPTION
GRANT INSERT  TO USER [myUser] WITH GRANT OPTION
GRANT DELETE  TO USER [myUser] WITH GRANT OPTION
GRANT EXECUTE TO USER [myUser] WITH GRANT OPTION

Optional [WITH GRANT OPTION] indicates that the role/user may pass/grant the permission to other user/role.

To grant privileges for specific object, for example grant SELECT/UPDATE/INSERT/DELETE for the [mytable] and EXECUTE for [myproc], execute:

USE mydb;
GO;
GRANT SELECT  ON [mytable] TO ROLE [myRole] WITH GRANT OPTION
GRANT UPDATE  ON [mytable] TO ROLE [myRole] WITH GRANT OPTION
GRANT INSERT  ON [mytable] TO ROLE [myRole] WITH GRANT OPTION
GRANT DELETE  ON [mytable] TO ROLE [myRole] WITH GRANT OPTION
GRANT EXECUTE ON [myproc]  TO ROLE [myRole] WITH GRANT OPTION

For user grant:

USE mydb;
GO;
GRANT SELECT  ON [mytable] TO USER [myUser] WITH GRANT OPTION
GRANT UPDATE  ON [mytable] TO USER [myUser] WITH GRANT OPTION
GRANT INSERT  ON [mytable] TO USER [myUser] WITH GRANT OPTION
GRANT DELETE  ON [mytable] TO USER [myUser] WITH GRANT OPTION
GRANT EXECUTE ON [myproc]  TO USER [myUser] WITH GRANT OPTION

The privileges specified per database object table/procedure always precedes the global databases' permissions

Deny privileges

The deny command creates a negative privilege(s). As with grant privileges, the denied privileges specified per database object (table/procedure,..) always will precedes the global databases' permissions. For example, if user is a member of role that has [SELECT] to all tables per database level, but has exclusively DENY'd [SELECT] permissions to the specific table, the attempt to select from the table will fail.

For role:

USE mydb;
GO;
DENY SELECT TO ROLE [myRole]

For user:

USE mydb;
GO;
DENY SELECT TO USER [myUser]

To deny privileges for specific object, for example grant SELECT/UPDATE/INSERT/DELETE for the [mytable] and EXECUTE for [myproc], execute:

USE mydb;
GO;
DENY SELECT  ON [mytable] TO ROLE [myRole] WITH GRANT OPTION

For user grant:

USE mydb;
GO;
DENY SELECT  ON [mytable] TO USER [myUser] WITH GRANT OPTION

Revoke privileges

Revoking privileges is permitted per database level, i.e. for all database objects (tables/procedures) or per specific table/procedure. For example, following Sql will revoke DELETE/UPDATE privileges for all tables from role [myRole] or user [myuser]:

USE mydb;
GO;
REVOKE UPDATE  FROM ROLE [myRole]
REVOKE DELETE  FROM ROLE [myRole]

or,

USE mydb;
GO;
REVOKE UPDATE FROM USER [myUser]
REVOKE DELETE FROM USER [myUser]

Revoking existing [mytable] tables' [UPDATE/DELETE] permissions;

USE mydb;
GO;
REVOKE UPDATE  ON [mytable] FROM ROLE [myRole]
REVOKE DELETE  ON [mytable] FROM ROLE [myRole]

or,

USE mydb;
GO;
REVOKE UPDATE  ON [mytable] FROM USER [myUser]
REVOKE DELETE  ON [mytable] FROM USER [myUser]

The privileges specified per database object table/procedure will be removed. For example, if user is a member of role that has revoked DELETE from all tables, but has exclusively DENY'ed DELETE permissions to the specific table, the attempt to delete from the table will fail, unless the exclusive tables' permission has been revoked.

Built-in roles

There are 3 types of roles: server, database and performance roles.

Server roles

Server roles apply globally to all databases. There are 2 roles (the administrator user will be added as the member):

• [Administrator]. Privileges to access any database, change cluster, basically do anything.
• [AdminAccessRole]. It's performance role that define the higher than normal execution query priority, no queuing and throttling. Therefore, use administrator account only for administration, troubleshooting purposes.

Database roles

• [DB_owner] - particular database administrative permissions. The user who creates the database becomes a member of [db_owner] group. The roles' permissions:
  • CREATE ROLE
  • DROP ROLE
  • ALTER MEMBERSHIP,
  • ALTER TABLE
  • ALTER PROCEDURE
  • ALTER DATABASE
  • BACKUP DATABASE
  • CREATE TABLE
  • CREATE PROCEDURE
  • SELECT
  • UPDATE
  • DELETE
  • INSERT
  • EXECUTE

• [Public] - a public database role. New users will be members of the system database [Public] role, in order to see the databases and tables. Public role includes permissions:
  • SELECT
  • SHOWPLAN
  • CREATE DATABASE
  • CREATE TABLE
  • CREATE PROCEDURE

Performance roles

Performance role controls the query execution priority and priority throttling (dynamic decrease) depending of amount of resources the transaction use. Each user can be a member of a single performance role. When user has no performance role bind or database is not secured, the default query priority and throttling will be used. The default parameters are set in registry, in addition, the query priority can be set in a connection string ("... Priority=[1,100]..").

Create a performance role

To create the performance role, execute SQL command:

CREATE performance ROLE [myperfrole]
priority <1,100>
Quants <ThrottleHalfQuants>
disk_reads <ThrottleHalfPageAccess>
page_faults <ThrottleHalfPageFaults>
net_reads <ThrottleHalfNetworkIO>

where constants,

• priority . The query execution priority from 1 to 100. The priority defines how aggressive the query should requesting resources: CPU, diskIO and network. For example: running 10+ infinite loop queries with no throttling and 100 priority will constantly access CPU resource. Therefore, CPU will become hot resource and access to it will be prioritized. Therefore, the response from the new query with priority 50 will be approximately 50% lower than a response from the same query running with the priority 100.
• quants . The amount of quants to consume before the query priority dynamically decreases by half. The quant is the time slice spends on CPU. The default value is 500 microseconds. When query to complete takes longer than that, the query will be suspended, additional quants allocated, and scheduled again. To turn off priority throttling by quants, set the variable to 0.
• disk_reads. Number of data pages accessed to throttle priority. To turn off priority throttling by disk reads, set the variable to 0.
• page_faults. Decrease priority by half when number of page faults occurs (when not enough free space in data pages cache, flush dirty page to disk in order to read other page to the memory). To turn off throttling set to 0.
• net_reads. The tcp sends to other nodes. The priority will be dynamically decreased like above constants. Set to 0 to turn off throttling.

Example:

CREATE performance ROLE [myperfrole]
priority 100
quants 100
disk_reads 100
page_faults 100
net_reads 100

Set performance role for a user

Simply add the role to the user. The example bellow, user [myuser] will inherit query execution parameters from role [myperfrole].

ADD PERFORMANCE ROLE [myperfrole] TO USER [myuser]

To remove performance role, execute:

REMOVE PERFORMANCE ROLE [myperfrole] FROM USER [myuser]

Alter a performance role

To alter [myperfrole] execute following SQL:

ALTER performance ROLE [myperfrole]
priority 80
quants 100
disk_reads 10
page_faults 0
net_reads 0

Altering performance role will affect all users bind to the performance role [myperfrole] execution control.

Delete the performance role

To delete [myperfrole] execute following SQL:

DROP ROLE [myperfrole]

Access permissions

The following permissions used to control access to the database objects:

Server permissions

• [CREATE DATABASE] - Allow user to create a new databases.
• [CHANGE PRIORITY] - Allow to change the query priority, either using [SET PRIORITY x], [SET THROTTLE], or, connection string priority parameter.
• [ALTER CLUSTER] - Allow alter the cluster. Add/drop nodes, add/split partition groups...
• [SHOWPLAN] - Allows reading the query execution plan.
• [MANAGE SESSION] - Kill connections, abort any session transaction.
• [CREATE USER] - Create new user permission.
• [DROP USER] - Drop user permission.

Database permissions

• [CREATE ROLE] - Create role permission.
• [DROP ROLE] - Drop role permission. Note: the user can drop any database role, except built-in roles.
• [ALTER MEMBERSHIP] - Allows add/remove roles for the user.
• [ALTER TABLE] - Alter/Drop/Truncate any database table.
• [ALTER PROCEDURE] - Drop procedures.
• [ALTER DATABASE] - Drop database permission.
• [BACKUP DATABASE] - Backup database permission.
• [CREATE TABLE] - Create table permission.
• [CREATE PROCEDURE] - Create procedure permission.
• [SELECT] - Read any tables' content.
• [UPDATE] - Update rows.
• [DELETE] - Delete rows.
• [INSERT] - Insert rows.
• [EXECUTE] - Execute procedure permission.

Table permissions

• [ALTER TABLE] - Alter/Drop/Truncate particular table permission.
• [SELECT] - Read tables' content.
• [UPDATE] - Update rows.
• [DELETE] - Delete rows.
• [INSERT] - Insert rows.

Stored procedure permissions

• [ALTER PROCEDURE] - Drop procedure permission
• [EXECUTE] - Execute particular procedure permission.
• [SHOWPLAN] - Show procedures' plan

Permission Id's

Each permission has unique ID value, which is used instead of privilege name in [syspermissions] table.

Permissions grant levels

The objects' permissions can either be granted per table/procedure, database or system levels. If the privilege doesn't exist per table, privileges per tables' database will be inspected, and, if database has no privileges, the system privileges will be used.

For example:
Grant [SELECT] permissions to any table in testdb database to user myuser:

USE tedtdb
GRANT SELECT TO USER myuser

Deny [SELECT] permissions to <mytable> table in <testdb> database to user <myuser>:

USE tedtdb
DENY SELECT ON [mytable] TO USER [myuser]

The user will fail to access [mytable], unless tables' DENY’ed privilege is REVOKE'd.

System stored procedures

Show User Roles

The procedure [show_user_roles] will show all the roles the user [myuser] has membership:

USE system;
EXEC show_user_roles [myuser]

Show User Permissions

The procedure displays, explicit users' permissions:

USE system;
EXEC show_user_permissions [myuser]

Show Role Permissions

The procedure displays, database roles' permissions:

USE system;
EXEC show_role_permissions [mydb],[myuser]

Example

Securing database with username and password