The uses of the SQL Server is to store, retrieve, and manipulate the data. One of the key features of the SQL Server is its ability to grant and manage permissions to different users and groups. SQL Server roles are a powerful tool that allows the database administrators to assign a set of permissions to a group of users with similar needs.
This article provides a guide on how to use the SQL Server roles, including an overview of the different types of available roles, examples of how to assign these roles to the users, and tips on how to manage these roles.
What Are SQL Roles?
Roles allow the DBA to manage the permissions more efficiently. We need to create the roles first. Then, we assign the permissions to the roles and then add the logins to the roles, accordingly.
The SQL Server supports mainly two types of roles:
- Fixed Server Roles: These roles already have a predefined set of permissions.
- User-Defined Server Roles: These roles can be changed to meet the requirements of your organization.
Types of SQL Server Roles
The SQL Server provides several built-in roles that can be used to assign permissions to the users. Here are some of the most common roles and their corresponding permissions:
- bulkadmin: The members of the bulkadmin fixed server role can run the “BULK INSERT” statement.
- sysadmin: This role has the highest level of permissions and can perform any action on the SQL Server instance including creating, modifying, and deleting the databases and logins.
- serveradmin: This role can configure the server-level settings such as memory allocation and network protocols, but it cannot modify the databases.
- securityadmin: This role can manage the logins and their permissions including creating and modifying the logins, roles, and passwords.
- processadmin: The processadmin fixed server role grants its members the ability to end or stop the processes that are currently executing within an instance of the SQL Server Database Engine.
- dbcreator: This has the permissions to create, modify, and remove the databases, but it does not possess the authority to execute any other administrative duties.
- diskadmin: This role can manage the disk files such as creating and deleting files and adding or removing disks.
- datareader: The datareader role has the capability to access and read all information that are stored in the user tables of a database.
- datawriter: The datawriter role possesses the authority to insert, update, and delete the data from all user tables within a database.
- ddladmin: This role can create, modify, and drop the DDL triggers and objects (views, tables, and stored procedures).
- public: By default, all SQL Server users, groups, and roles are automatically included in the public fixed server role.
Creating the User-Defined Server Role Using GUI
Creating a user-defined server role in SQL Server can be done through GUI using the SQL Server Management Studio (SSMS). To create a user-defined server role using the GUI in SSMS, follow these steps:
1. Launch the SQL Server Management Studio (SSMS) and establish a connection to the specific SQL Server instance.
2. Go to Security -> Server Roles-> New Server Role.
3. Specify a name for the new role in the General page.
4. Specify the server-level securables that the role will have permissions on in the Securables page.
5. Add any users or other roles in the Members page.
Creating and Using the Server Role Through T-SQL
Creating and using a server role using T-SQL in the SQL Server is another way to manage the server-level permissions. To create a user-defined server role using T-SQL, we have to follow these steps:
1. Open a new query window and execute the following code to create the new server role:
USE [master]
GO
CREATE SERVER ROLE [junior DBA]
GO
In this example, we create a new server role – “junior DBA”.
2. Execute the following code to grant permissions to the new role:
GO
GRANT CREATE ANY DATABASE --<< now can create db
TO [junior DBA]
GO
In this example, we allow the permission to the new role, “junior DBA”, to create the databases.
TO [junior DBA]
GO
In this example, the “GRANT VIEW ANY DATABASE” command is used to grant a server-level permission that allows the role to see the metadata for any database on the server. In this case, we grant this permission to the “junior DBA” server role that we created earlier.
However, it’s important to note that granting the “VIEW ANY DATABASE” permission does not give the user or the role permission to access any data within the databases – it simply allows them to see the metadata (such as database names and schemas).
3. Add a login or user account to the new role by executing the following code:
ALTER SERVER ROLE [junior DBA]
ADD MEMBER [Papan]
GO
ALTER SERVER ROLE [junior DBA]
ADD MEMBER [Rima]
GO
In this example, we add the “Papan” and “Rima” accounts as members of the “junior DBA” role.
Conclusion
The SQL Server roles provide a powerful tool to manage the permissions in a database environment. By assigning roles to the users, you can ensure that they have the appropriate level of access to the data that they need without giving them unnecessary privileges. Whether you are managing a small database or a large enterprise system, understanding how to use the SQL Server roles is an important skill that can help you maintain the security and integrity of your data.
