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.)
|Windows||Windows Group||Securables include files and registry keys; Windows uses ACLs (Access Control Lists) to define which principal has access|
|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:
- take ownership
- view definition
But the principal can only perform the action if both of the following are true:
- permission has been granted explicitly to the principal or a collection that includes the principal
- 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|
- 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|
|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|
|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
2) –Returns information about the roles in the current database
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
5) –Returns a report that has information about user permissions for an object, or statement permissions, in the current database