Creating a new database role is an essential aspect of database-level security. In order to control access to the database, roles are created and assigned specific permissions and membership.
The CREATE ROLE statement in SQL Server allows users to create a new role as well as grant specific permissions to that role. In this article, we will cover the CREATE ROLE statement in SQL Server, including how to create a new role, how to specify the owner of the role, how to grant permissions to the role, and how to add members to the role.
Creating a New Database Role
The CREATE ROLE statement in SQL Server is used to create a new database-level securable – essentially, a security boundary inside the database that can be used to control access to database objects. To create a new role, the following syntax can be used:
CREATE ROLE role_name
[ AUTHORIZATION owner_name ]
The role_name is the name of the new role that you want to create. The AUTHORIZATION clause is optional but if specified, it will assign the owner_name as the owner of the new role.
If the AUTHORIZATION clause is not specified, the owner_name defaults to the user who is currently executing the CREATE ROLE statement. For example, let’s assume that we have a sample database called BikeStores, and we want to create a new role called Sales.
The syntax for creating this new role with the current user as the owner would be as follows:
CREATE ROLE Sales;Specifying the Owner of the Role
If you want to specify a different user or login as the owner of the role, you can use the AUTHORIZATION clause. For example, let’s say that we want to create the Sales role but we want a user called SalesManager to be the owner instead of the current user.
The syntax for creating this new role with SalesManager as the owner would be as follows:
CREATE ROLE Sales AUTHORIZATION SalesManager;This would create the Sales role with SalesManager as the owner.
Granting Permissions and Adding Members to the Role
Once a new role has been created, it can be granted permissions that determine what actions members of that role can perform in the database. Permissions can be granted to the role using the GRANT statement.
The syntax for granting permissions to a role is as follows:
GRANT permission_name [, permission_name]...
TO role_name [, role_name]...
[ WITH GRANT OPTION ]
[ AS grantor ]The permission_name specifies the permission to be granted to the role, and the role_name specifies the name of the role that you want to grant the permission to. The WITH GRANT OPTION is an optional clause that allows members of the role to grant the same permission to other users and roles.
The grantor is the user or role that is granting the permission, which is also an optional clause. For example, let’s say that we want to grant the Sales role SELECT, INSERT, DELETE, and UPDATE privileges on a schema called Sales.
The syntax for granting these privileges would be as follows:
GRANT SELECT, INSERT, DELETE, UPDATE
ON schema::Sales TO Sales;This would grant the SELECT, INSERT, DELETE, and UPDATE privileges on the Sales schema to the Sales role. Adding users to the role is also a crucial step in creating a functional database role.
To add a user to a role, you can use the ALTER ROLE statement. The syntax for adding a user to a role is as follows:
ALTER ROLE role_name
ADD MEMBER user_name [,user_name]… The role_name specifies the name of the role that you want to add the user to, and user_name specifies the name of the user that you want to add to the role.
For example, let’s say that we want to add a user called SalesAgent to the Sales role. The syntax for adding this user to the role would be as follows:
ALTER ROLE Sales
ADD MEMBER SalesAgent;This would add the SalesAgent user to the Sales role, allowing them to perform the actions that the Sales role has been granted access to.
Conclusion
Database roles play a crucial role in database-level security. The CREATE ROLE statement in SQL Server allows users to create new roles that can be used to control access to database objects.
By using the correct syntax and keywords, users can specify the owner of the role, grant permissions to the role, and add members to the role. With these steps, a functional and secure database role can be created to help control and manage database access.
3) Creating a New Role Owned by a Fixed Database Role
In addition to being owned by a specific user or login, a new role can also be owned by a fixed database role such as db_securityadmin. Fixed database roles are pre-defined database-level roles that are created automatically when a database is created.
These roles have assigned permissions and members that cannot be changed, but they can own custom roles. To create a new role owned by a fixed database role, the same syntax that we used earlier can be used with the addition of specifying the fixed database role as the owner of the new role.
For example, let’s say that we want to create a new role called OrderProcessing that is owned by the db_securityadmin fixed database role. The syntax for creating this new role would be as follows:
CREATE ROLE OrderProcessing AUTHORIZATION db_securityadmin;This would create the OrderProcessing role and set db_securityadmin as the owner of the role.
4) Examining the Roles
After creating roles, it can be helpful to view the roles and their members to ensure that everything was configured correctly. SQL Server provides two views to examine roles and their members: sys.database_principals and sys.database_role_members.
The sys.database_principals view provides information about all database-level principals – including users, groups, and roles – in the current database. This view contains columns that provide information about each principal, such as the name, type, and owner of the principal.
To view all the roles in the current database, we can use the following SQL query:
SELECT name, type_desc, is_fixed_role, owner_sid
FROM sys.database_principals
WHERE type = 'R'This query selects the name, type_desc, is_fixed_role, and owner_sid columns from the sys.database_principals view, filtering only for rows where the type column is equal to ‘R’, which indicates that the principal is a role. The type_desc column provides a textual description of the type of principal, while the is_fixed_role column indicates whether the role is a fixed database role or not.
The owner_sid column provides the owner of the role as a Security Identifier (SID). For example, let’s view all the roles in the BikeStores sample database.
The SQL query would be as follows:
SELECT name, type_desc, is_fixed_role, owner_sid
FROM BikeStores.sys.database_principals
WHERE type = 'R'This would display all the roles in the BikeStores database along with their descriptions, whether they are fixed or not, and the owner of the role as a SID. The sys.database_role_members view provides information about the members of each role.
This view contains columns that provide information about each role and its members, such as the role name and the member name. To view the members of a specific role, we can use the following SQL query:
SELECT rol.name AS [Role],
mem.name AS [Member]
FROM sys.database_role_members AS dbrm
JOIN sys.database_principals AS mem
ON dbrm.member_principal_id = mem.principal_id
JOIN sys.database_principals AS rol
ON dbrm.role_principal_id = rol.principal_id
WHERE rol.name = 'role_name'This query selects the role name and member name from the sys.database_role_members view, joining on sys.database_principals twice to get the actual role and member names.
The WHERE clause filters the results to only show members of the specified role. For example, let’s view the members of the Sales role in the BikeStores sample database.
The SQL query would be as follows:
SELECT rol.name AS [Role],
mem.name AS [Member]
FROM BikeStores.sys.database_role_members AS dbrm
JOIN BikeStores.sys.database_principals AS mem
ON dbrm.member_principal_id = mem.principal_id
JOIN BikeStores.sys.database_principals AS rol
ON dbrm.role_principal_id = rol.principal_id
WHERE rol.name = 'Sales'This would display all the members of the Sales role, including users or roles that have been added using the ALTER ROLE statement.
Conclusion
Creating and managing database roles is an important aspect of database-level security. By using the CREATE ROLE statement, users can create new roles and assign permissions and membership as needed.
Roles can be owned by specific users or logins, as well as fixed database roles such as db_securityadmin. By using the sys.database_principals and sys.database_role_members views, users can examine roles and their members to verify that everything was set up correctly.
With the right planning and management, database roles can greatly improve security and access control in a SQL Server database. In conclusion, the SQL Server CREATE ROLE statement is a powerful tool for creating and managing database roles.
Roles can be owned by fixed database roles or specific users or logins, and permissions and membership can be assigned using the GRANT and ALTER ROLE statements. The sys.database_principals and sys.database_role_members views can be used to examine roles and their members.
Database roles are a crucial aspect of database-level security, improving access control and ensuring that database objects are properly protected. By using these tools and best practices, users can create a more secure and manageable SQL Server database that meets the needs of their organization.