Managing SQL Server 2005 Security

The term ‘Principals’ in the following table refers to authenticated identities in SQL Server; ‘Securables’ refers to objects that SQL Server regulates access to (databases, keys, users, logins, etc.)

Level Principals Notes
Windows Windows Group Securables include files and registry keys; Windows uses ACLs (Access Control Lists) to define which principal has access
User Account
Local User Account
SQL Server SQL Server Login -instance access but not db except, perhaps, master
Server Role -a role here is a group of logins
Database User -what is normally used to access a db
Database Role -a role here is a group of users
Application Role -enables an application to run with its own, user-like permissions; has no members except when used by an application that knows the name and the password

You can Grant, Revoke or Deny permissions to SQL Server Securables by Principal for any of the following actions:

  • create
  • alter
  • drop
  • control
  • connect
  • select
  • execute
  • update
  • delete
  • insert
  • take ownership
  • view definition

But the principal can only perform the action if both of the following are true:

  1. permission has been granted explicitly to the principal or a collection that includes the principal
  2. permission has not been explicitly denied to the principal or a collection that includes the principal

For example: Ben is given ‘select’ permission on a table but he is a member of the Windows Group HRAdmin. If HRAdmin doesn’t have an explicit ‘grant’ (or it was granted but was revoked at some time) then he won’t be allowed to use select on the table.

Example scenarios for differing level permissions.

Principal Permission for Select
Public Deny Grant Grant Deny Grant Revoke
HRAdmin Deny Grant Grant Revoke Revoke
Ben Grant Grant Deny Revoke Revoke
for select
Can’t Can’t Can Can’t Can Can’t
  • In other words, ‘Deny’ always wins the battle.
  • Revoke is an indeterminate state. When permission is revoked it means ‘maybe’ and will look at a higher level (in the collection containing the principal involved) the resolve the question.
  • Server Roles are higher level groups within SQL Server for logins. The admin server roles are outside of the above rules in their particular type of securables and look straight through permissions. See the following table for the list.
Fixed Server Roles
Role Description
sysadmin System administrators: perform any activity i.e.: sa
securityadmin Security administrators: manage and audit server logins
serveradmin Server administrators: configure server wide settings
setupadmin Setup administrators: configure replication and linked servers
processadmin Process administrators: manage SQL Server processes
diskadmin Disk administrators: manage disk files
dbcreator Db creators: create, alter and drop dbs
bulkadmin Can execute BULK INSERT statements
public Automatic role of which all logins are a member; can view any db
  • dbo and guest are special users that are created in all dbs by default.
  • dbo cannot be deleted and has no login.
  • guest is usually denied rights to log in by default and so is only a security risk if someone has granted it extra permissions. Usually you’d want to provide users with a ‘real’ user which you could tailor for their specific requirements.
  • Unlike server roles, you can add your own database roles but usually the roles provided by MS are sufficient (don’t want to reinvent the wheel because then the DBA will have to maintain it).
Fixed Database Roles
Role Description
db_owner Can perform all configuration and maintenance activities on the db, and can also drop the db, really is the god of the db and can perform any db role activity
db_securityadmin Can modify role membership, manage permissions and create schemas. Adding principals to this role could enable unintended privilege escalation
db_accessadmin Can add or remove access to the db for Windows logins, Windows groups, and SQL Server logins, roles and users
db_backupoperator Can back up the db
db_ddladmin Can run any Data Definition Language (DDL) command in a db (add, modify and drop db objects)
db_datawriter Can add, delete, or change data in all user tables
db_datareader Can read all data from all user tables
db_denydatawriter Cannot add, modify, or delete any data in the user tables within a db
db_denydatareader Cannot read any data in the user tables within a db
public Special role which maintains default permissions and cannot be deleted

SQL Server supports the use of keys and digital certificates if that level of data security is required for columns or tables. Built in functions can be used to encrypt and decrypt data.  I’ve seen demonstrations where encryption and decryption are fairly quick.  Personally I don’t think this level of complication is better than a good strategy to keep your network and entire db secure.

Utilities that can explore Security
1)–Provides information about logins and the users associated with them in each database
exec sp_helplogins
2) –Returns information about the roles in the current database
exec sp_helprole
3) –Returns information about the members of a specific SQL Server fixed server role
exec sp_helpsrvrolemember ‘sysadmin’
4) –Reports information about Windows groups with accounts in the current database
exec sp_helpntgroup
5) –Returns a report that has information about user permissions for an object, or statement permissions, in the current database
exec sp_helprotect


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s