SQL Server 2005 : In a nutshell (Part 2)


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



Can perform any activity in SQL Server


Can set server-wide configuration options, shut down the server


Can manage linked servers and startup procedures


Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords


Can manage processes running in SQL Server


Can create, alter, and drop databases


Can manage disk files


Can execute BULK INSERT statements

… and fixed database roles:

Fixed database role



Has all permissions in the database


Can add or remove user IDs


Can manage all permissions, object ownerships, roles and role memberships


Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements


Can issue DBCC, CHECKPOINT, and BACKUP statements


Can select all data from any user table in the database


Can modify any data in any user table in the database


Cannot select any data from any user table in the database


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


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