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!