Security in SQL Server

Principles¶

Users have permissions established by the following four elements.

By way of membership in a Fixed Server Role. Rights automatically granted to members of this group cannot be rescinded via a DENY statement.
By way of membership in a Fixed Database Role. Rights automatically granted to members of this group cannot be rescinded via a DENY statement.
By way of membership in a user-defined database role
Directly to their user login

Fixed Server Roles¶

Role	Name	Description
`sysadmin`	System Administrators	Performs any activity in SQL Server
`securityadmin`	Security Administrators	Manages server logins
`serveradmin`	Server Administrators	Configures server-wide settings
`setupadmin`	Setup Administrators	Adds/removes linked servers, and execute some system stored procedures, such as `sp_serveroption`
`processadmin`	Process Administrators	Manages processes running in SQL Server
`diskadmin`	Disk Administrators	Manages disk files
`dbcreator`	Database Creators	Creates and alters databases

Fixed Database Roles¶

Role	Description
`public`	Default rights for all users
`db_owner`	Performs the activities of all database roles, as well as other maintenance and configuration activities in the database
`db_accessadmin`	Adds or removes Windows NT groups, Windows NT users, and SQL Server users in the database
`db_datareader`	Sees all data from all user tables in the database
`db_datawriter`	Adds, changes, or deletes data from all user tables in the database
`db_ddladmin`	Adds, modifies, or drops objects in the database
`db_securityadmin`	Manages roles and members of SQL Server database roles, and can manage statement and object permissions in the database
`db_backupoperator`	Backs up the database
`db_denydatareader`	Sees no data in the database
`db_denydatawriter`	Changes no data in the database