Security in SQL Server

Principles

Users have permissions established by the following four elements.


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