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!!
Regards
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
