Know Your (Pre-Defined DB) ROLE!

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!


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on July 6, 2012, in Maintenance, Security and tagged . Bookmark the permalink. Leave a comment.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: