Overview of the SQL Server Security Model and Security Best Practices

There are three types of roles in SQL Server 7.0 and 2000:

• Fixed server roles
  
  
• Fixed database roles
  
  
• Application roles

Fixed Server Roles

Fixed server roles are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed Server Role Descriptions

• sysadmin: Can perform any activity in SQL Server
  
  
• serveradmin: Can set server-wide configuration options, shut down the server
  
  
• setupadmin: Can manage linked servers and startup procedures
  
  
• securityadmin: Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
  
  
• processadmin: Can manage processes running in SQL Server
  
  
• dbcreator: Can create, alter, and drop databases
  
  
• diskadmin: Can manage disk files
  
  
• bulkadmin: Can execute BULK INSERT statements

Here is a list of stored procedures that are helpful in managing fixed server roles:

• sp_addsrvrolemember: Adds a login as a member of a fixed server role
  
  
• sp_dropsrvrolemember: Removes an SQL Server login, Windows user or group from a fixed server role
  
  
• sp_helpsrvrole: Returns a list of the fixed server roles
  
  
• sp_helpsrvrolemember: Returns information about the members of fixed server roles
  
  
• sp_srvrolepermission: Returns the permissions applied to a fixed server role

Fixed Database Roles

Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed Database Role Description

• db_owner: Has all permissions in the database
  
  
• db_accessadmin: Can add or remove user IDs
  
  
• db_securityadmin: Can manage all permissions, object ownerships, roles and role memberships
  
  
• db_ddladmin: Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
  
  
• db_backupoperator: Can issue DBCC, CHECKPOINT, and BACKUP statements
  
  
• db_datareader: Can select all data from any user table in the database
  
  
• db_datawriter: Can modify any data in any user table in the database
  
  
• db_denydatareader: Cannot select any data from any user table in the database
  
  
• db_denydatawriter: Cannot modify any data in any user table in the database