security cribsheet
Integrated security
'Integrated security' can only be used if SQL Server is participating in the Windows Network. The advantages are password-encryption, password-aging, domain-wide accounts and windows administration. It is based on an "access token" which contains the user's unique security ID or sid, which is then used by the client to gain access to network resources such as SQL Server without having to supply login credentials again. If a user has an access token, then it means that he has previously passed authentication checks.
Fixed Server Roles
Logins can, where necessary, be assigned to a number of fixed server roles so that the SA can delegate some, or all, of the administration task. These roles are:
- sysadmin
can perform any activity, and has complete control over all database functions. - serveradmin
can change server configuration parameters and shut down the server. - setupadmin
can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption. - securityadmin
can create and manage server logins and auditing, and read the error logs. - processadmin
can manage the processes running in SQL Server. - dbcreator
can create, alter, and resize databases. - diskadmin
can manage disk files.
Database Roles
A Database Role is a collection of database users. Instead of assigning access permissions to users, one can assign them to Roles, a collection of users who have a common set of requirements for accessing the database. Used to be called a group.
Fixed Database Roles
There are several fixed, pre-defined database roles that allow various aspects of the database administration to be assigned to users. Members of fixed database roles are given specific permissions within each database, specific to that database. Being a member of a fixed database role in one database has no effect on permissions in any other database. These roles are…
- db_owner
allows the user to perform any activity in the database. - db_accessadmin
allows the user to add or remove Windows NT groups, users or SQL Server users in the database. - db_datareader
allows the user to view any data from all user tables in the database. - db_datawriter
allows the user to add, change, or delete data from all user tables in the database. - db_ddladmin
allows the user to make any data definition language commands in the database. - db_securityadmin
allows the user to manage statement and object permissions in the database. - db_backupoperator
allows the user to back up (but not restore) the database. - db_denydatareader
will deny permission to select data in the database. - db_denydatawriter
will deny