Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Security in SQL Server

RSS
Modified on Fri, Jan 09, 2009, 2:26 PM by Administrator Categorized as Quick Reference, 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.