SQL Server Database Level Security – Part 2

Hello Folks,

You would have seen my last article-post which describe about how to grant access to the database from both the sides, i.e.  the login side as well as database side. If you want to refer it again, then click on this link;

As I told you previously this article-post will be on Fixed Database Roles.

Fixed Database Roles:

SQL Server includes a Fixed Database Role. They are mainly intended to organize the administrative tasks. A user can also have more than one role. The fixed database roles include the following:

  • Db_accessadmin: It helps a user to access a database, but not manage the database security level.
  • Db_backupoperator: It performs backup, checkpoints, and DBCC commands, but cannot manage the database level security.
  • Db_datareader: It is used to read all the data in the database. It is moreover an equivalent of a grant on all objects, and it can be overridden by a deny permission.
  • Db_datawriter: It is used to write all the data in the database. It is moreover an equivalent of a grant on all objects, and it can be overridden by a deny permission.
  • Db_ddladmin: It helps to issue DDL commands like create, alter, and drop.
  • Db_denydatareader: It is being used to read from any table in the database. This Deny can easily override the Grant permission.
  • Db_denydatawriter:  It is mainly being used to block modifying data in any table in a database. They would override any object-level grant.
  • Db_owner: It has all the permissions in the database. This is also not equivalent to server sysadmin role. An object-level deny will override membership in this role.
  • Db_securityadmin: It helps to manage the database-level security roles and permission.

Assigning Fixed Database Roles:

It can be done in two ways:

  • From the Login Side.
  • From the Database Side.

Login Side:

Under the Security tab, then select Logins, and then select the Login Name and right-click on this, and then click on Properties-



So according to different fixed roles explained above, you can check the options from above and the different logins can also have multiple roles.

Database Side:

Under the Databases Tab, Select the Database Name, and then under the Security Tab, go on select the User Name which you want to select. Right-click on it and click Properties.


Click the Ok button.


Manage Roles:

With the help of Stored Procedure, a user can also be able to create any desired roles:

EXEC sp_addrole 'abc'

You can also delete the roles, with the help of Stored Procedure:

EXEC sp_droprole 'abc'

Well, this was all about Database Level Security for SQL Server, and with this we come to an end of this sequel.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.