This article discusses the database level roles to manage permission to users in a SQL Server database.
All the users of a database may not have the same access level. Depending upon their work or role, some users may have only read access, some may have read and write access while a handful of users may have full access to the database. SQL Server facilitates us to easily manage the user permissions using the database roles.
The database-level roles in SQL Server are of two types. The first one is fixed-database roles and the other is user-defined database roles. As the name suggests, the fixed-database roles are fixed and we cannot add or remove permission to it. On the other hand, the user-defined roles are defined by users, i.e. us and we can add or remove database permissions as per needed.
The fixed-database roles are the one provided by the SQL Server and contain the basic database permissions and exist on each database. It includes db_owner, db_securitadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datareader, db_datawriter, db_denyreader, and db_denywriter.
Fig-1: SQL Server Database Roles
- db_owner – Uses with SQL Server db_owner database role have full access to the database. They can perform all the configurations in a database including dropping the database.
- db_sercurityadmin – Members of db_sercurityadmin can manage the user roles in a database. They can create a new user login and assign them roles.
- db_accessadmin – Users who are assigned db_accessadmin can add or remove access to the database for Windows login, Windows groups, and SQL Server logins.
- db_backupoperator – Members of the db_backupoperator can backup the database.
- db_ddladmin – Users with SQL Server db_ddladmin database role can run any Data Definition Language (DDL) statements like CREATE, ALTER against the database. It means that they can add new objects (tables, views, functions, etc.), and alter and drop them as well.
- db_datareader – Members of the SQL Server db_datareader role can only read from the database. They can read from all user tables, but they can’t add any data or alter the database tables.
- db_datawriter – Users with SQL Server db_datawriter database role have permission to add, edit, or delete data on all user tables but they cannot alter the schema of the tables.
- db_denydatareader – Users who have the db_denydatareader database role do not have permission to read data from any of the database tables.
- db_denydatawriter – MEmbers of the db_denydatareader database role cannot add, edit, or delete data from the tables of the database.
User-defined Database Roles
In most cases the SQL Server fixed-database roles will be sufficient to give database access to a user but there are times when we need to give custom permissions to users. Here, we will see how to define a custom database role in SQL Server.
In SQL Server we write stored procedures to do any transactions or multi-statement operations, but if you have noticed, SQL Server does not have a fixed database role to allow executing stored procedures only. So, we will have to create a database role if we want a role in executing the stored procedure only. In the following part of this article we will create a user defined role named db_executor in SQL Server that provides permission to execute a stored procedure.
The syntax for creating a user-defined role in SQL Server is as follows:
CREATE ROLE <role_name>
Syntax for creating role
So, the query to db_executor role will be as follows:
CREATE ROLE db_executor
Query to create db_executor role
Now that we have created a role, we should assign permissions to it. The query to add execution permission to db_executor role is as follows:
GRANT EXECUTE TO db_executor
Query to add EXECUTE permission to db_executor
After this, we will have the SQL Server db_executor role in the database that provides permission to execute a stored procedure.
Fig-2: db_executor role
Still need some help? Connect with a live SQLExpert here for some 1 on 1 help. Your first session is always free!