SQL Server 2005 : In a nutshell (Part 2)


Security

The security model has changed slightly in SQL 2005 (at least in the way we look at security). It introduces the concepts of Principles, Securables and Permissions. Principles are objects that have a security context and will request resources for example windows logins, SQL Server logins, database users and database roles.  Securables are resources that a Principle needs to access such as tables, views, stored procedures etc. Finally Permissions are what are applied to Principles in order for them to access Securables, or in english… the ability for user domain\bob.smith to drop tables. Permissions include Grant (grant permissions to other principles), Revoke (remove) or Deny (explicitly restrict). One last point on Securables is Securable Scope. This essentially is the scope of nested access so for example a database and then tables within that database.

The access to databases still remains in that a database user must be mapped to a database login and then have database (fixed database roles) or server level (fixed server roles) roles assigned. Members of the sysadmin fixed server role has full permissions on all objects on all system and user databases. What follows is the summary of fixed server roles:

Fixed server role

Description

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

… and fixed database roles:

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

In order to understand the security mode, I always tend to find that a visual summary of the security levels is useful. The below shows the 3 security levels that SQL Server uses (Windows, Server Level, Database Level).

SQL Server Security Model
Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s