Database Level Roles in SQL Server

Database level roles in SQL Server, they can control/ restrict the permissions on database.

The following are various fixed roles in database level.

  1. db_datareader
  2. db_datawriter
  3. db_denydatareader
  4. db_denydatawriter
  5. db_ddladmin
  6. db_backupoperator
  7. db_accessadmin
  8. db_securityadmin
  9. db_owner

db_datareader  –> Members of this role can read all data from all tables in a database.

db_datawriter   –> Members of this role can create/change/delete  all user tables in a                                              database.

db_denydatareader  –>Members of this role can’t read data from user tables in a                                                                 database.

db_denydatawriter  –> Members of this role can’t create/change/delete  all user tables                                                in a database.

db_ddladmin          –> Members of this role can run all DDL (create,alter,drop)                                                              commands in a database.

db_backupoperator   –>Members of this role can take backup the database.

db_accessadmin         –> Members of this role can add/remove access to the database                                                      from windows logins/windows groups/SQL Server logins.

db_securityadmin        –> Members of this role can modify role memberships/role                                                             permissions in a database.

db_owner                      –> Members of this role can do all operations like configuring,                                                             monitoring and drop database also , it becomes an admin at                                                              database level.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s