Yesterday we talked about the Rock and how he can help you with SQL Server security ala “know your role.”

Do you know your SQL Server pre-defined database security roles?

These pre-defined roles will allow their respective members to perform defined sets of activities within each database.  It is important to realize that these exist in every database and you cannot drop these roles.  They are as follows:

• db_owner Role: This is the Mac Daddy of fixed database roles as it can perform all configuration and maintenance activities on the database including dropping the database.  It is the only role that members can add additional users to this role.
• db_securityadmin Role:  Members of this role can modify role membership (except db_owner) and manage permissions.
• db_accessadmin Role: Members of this role can add or remove access to the database for Windows logins, Windows groups, as well as SQL Server logins.
• db_backupoperator Role: Members of this role can back up the database, that is all.
• db_ddladmin Role: Members of this role can run any Data Definition Language (DDL) command in a database, in other words they can run ALTER, CREATE, DISABLE TRIGGER, ENABLE TRIGGER, DROP, TRUNCATE, and UPDATE STATISTICS statements.
• db_datawriter Role: Members of this role can add, delete, or change data in all user tables.
• db_datareader Role:  Members of this role can read all data from all user tables.
• db_denydatawriter  Role: Members of this role cannot add, modify, or delete any data in the user tables within a database.
• db_denydatareader Role: Members of this role cannot read any data in the user tables within a database.

There are several of these that I rarely use, but they are there if I need them.  Like the Rock says “know your role!”   Enjoy your weekend!

In the famous words of The Rock “know your role and shut your mouth!”  Well mainly we were going with “know your role.”

Do you know your SQL Server security roles?

A SQL Server role defines exactly what a user can and cannot do inside a database.  It is also possible for multiple users be it AD users and groups or SQL Server users can share this role.  Roles are similar to AD groups in Windows security except  they only control security within databases.

Today we will discuss the fixed roles which are setup across the server instance and apply to all objects on the server.  They are as follows:

• SysAdmin Role: This is the Mac Daddy of roles as it can perform any and all actions on the server.
• ServerAdmin Role: This role can change configuration options on the server.  They can also shut down the server, choose them wisely.
• SetupAdmin Role: This role can manage linked servers, SQL Server startup options and tasks.
• SecurityAdmin Role: This role can only manage server security.  They can also reset passwords saving you from the mundane task, if you have your own minions.
• ProcessAdmin Role: This role has the ability to kill processes running on the SQL Server aka SPID killer aka Die SPID die!
• DbCreator Role:  This role can create, alter, drop and restore databases, which is pretty powerful just now Genie from Aladdin powerful.
• DiskAdmin Role: This role can manage the SQL Server disk files.
• BulkAdmin Role: This role can only run the bulk insert command (I have personally never seen used before).
• Public Role:  Every user belongs to the public role by default and therefore you cannot change membership to this role.  Keep in mind if you assign any permissions to this role it will apply across the entire instance.

There are several of these that I rarely use, but they are there if I need them.  Like the Rock says “know your role!”   Tomorrow we will discuss pre-defined database roles.  Enjoy!