SQL Server Database and Server Roles for Security and Permissions

Problem

SQL Server is one of the most used relational database management systems in many organizations. It is mainly used to store, manage, and retrieve data with ease. Apart from this, SQL Server is popular for data security, including encryption, data masking, and role-based access control.

Today, we will discuss role-based access control (RBAC) in SQL Server. Using RBAC, you can assign specific permissions to users according to their roles within the server. There are different types of roles in SQL Server, which can be confusing. Here, we will discuss the distinctions between SQL Server and Database roles, helping us to manage security more effectively.

Solution

Let’s first understand the roles. There are two types of roles in SQL Server: 1) SQL Server Roles and 2) Database Roles.

What are SQL Server Roles?

SQL Server roles are predefined sets of permissions used to control access to server resources. They are created at the server level and typically assigned to logins or other server roles, which helps administrators manage permissions and security for the entire SQL Server instance. SQL Server roles are like Windows groups, allowing for easy management and assignment of permissions to multiple users.

Types of SQL Server Roles

There are three types of SQL Server roles: fixed server, user-defined server, and application.

Fixed SQL Server Roles –  Fixed server roles are predefined sets of server-level permissions that cannot be modified or deleted. These roles are created during the installation of SQL Server. This includes one of the important ‘sysadmin‘ roles, which has “God-level control” over the entire SQL Server instance, and other specialized roles like bulkadmin, dbcreator, diskadmin.

User-Defined SQL Server Roles – There are multiple instances when you need custom sets of permissions based on your business needs. Here, user-defined server roles come into the picture; these are not predefined roles. User-defined server roles will allow you to create custom sets of permissions based on your specific needs. These roles granted to logins or only other user-defined server roles provide more control over access to server-wide resources.

SQL Server Application Roles –  The above-mentioned roles are mostly assigned to individual users. This third type of role is like user-defined server roles called Application Roles. These roles are created for applications only and used by applications instead of any individual users. These special roles let applications borrow permissions for a short time to complete the task, keeping regular users and app users separate and safe.

Key Features of SQL Server Roles

• Scope: Server-wide
• Creation: Created at the server level
• Assignment: Assigned to logins or other roles
• Permissions: Control access to server resources (databases, logins, etc.)

Example: SQL Code to Create a SQL Server Role

1. Create a SQL Server Role. Replace [role_name] with the desired name for your new server role.

CREATE ROLE [role_name];

2. Assign the User to the Role. Replace [role_name] with the name you chose in Step 1 and [user_name] with the username you want to assign the role to.

ALTER ROLE [role_name] ADD MEMBER [user_name];

Note:

• You need to have sufficient permissions (e.g., sysadmin server role) to create server roles and manage user memberships.
• This code snippet only creates the role and assigns the user. You’ll need to grant specific permissions to the role itself to control user access within the server.

Example: Granting Permissions to the Role

You can use the GRANT statement.

GRANT CONNECT TO SERVER TO [role_name];

This grants the “Connect to Server” permission to the newly created role. You can explore other permission options based on your needs.

How to Check Server Roles Using SSMS

1. Open SSMS and connect to your SQL Server.
2. In the Object Explorer, navigate to Security > Server Roles.
3. Expand the Server Roles. You will see all the predefined and user-defined roles listed.

Alternatively, you can use SQL Query:

SELECT NAME 
FROM sys. server_principals
WHERE type_desc = 'SERVER_ROLE';

What are SQL Server Database Roles?

The Database Roles, as the name suggests, are specific to control databases and database objects. Unlike server roles, these roles are created and managed at the database level and can be assigned to database users and other roles within the same database they are created. These roles are a more controlled approach to managing permissions in a SQL Server instance as different users may have different levels of permissions.

Types of SQL Server Database Roles

There are also three types of database roles: fixed database, user-defined database, and application.

Fixed SQL Server Database Roles – Fixed database roles are like fixed server roles in that they cannot be modified or deleted. However, they are limited to the specific database in which they were created. The default fixed database role is ‘db_owner’, which has full control over the entire database and other roles like db_accessadmin, db_backupoperator,and db_datareader.

User-Defined SQL Server Database Roles – User-defined database roles allow for the creation of custom sets of permissions within a specific database. These roles can be assigned to users or other user-defined database roles, allowing for more granular control over access to objects within that database.

SQL Server Application Roles – Like SQL Server roles, application roles at the database level are intended for use by applications rather than normal users. They enable applications to temporarily assume permissions and perform actions on behalf of the role, providing an added layer of security.

Key Features

• Scope: Database-specific
• Creation: Created at the database level
• Assignment: Assigned to database users or other roles
• Permissions: Control access to specific database objects (tables, views, etc.)

Example: SQL Code to Create a Database Role

1. Create a Database Role

CREATE ROLE [role_name] AUTHORIZATION [user_name]

• Replace the following:
  • [role_name]: The desired name for your new database role.
  • [user_name]: The username who will own (own as in "be authorized by") the role. This user doesn't necessarily need to be the one assigned to the role.

This statement combines the CREATE ROLE and AUTHORIZATION clauses in a single line. The AUTHORIZATION clause specifies the user who will “own” the database role. This doesn’t necessarily restrict who can be assigned to the role, but it determines who can manage the role’s permissions later (e.g., adding/removing members and granting/revoking permissions to the role).

2. Assigning a User to the Database Role

ALTER ROLE [role_name] ADD MEMBER [user_name];

• Replace the following:
  • [role_name]: The name of the database role you created.
  • [user_name]: The username you want to assign to the database role.

This will grant the user the permissions associated with the database role.

Note:

• You need to have the db_owner role or equivalent permissions on the database to create database roles and manage user memberships.
• Remember to grant specific permissions to the database role itself to control user access within the database. You can use the GRANT statement for this purpose.

How to Check Database Roles Using SSMS

1. In SSMS, navigate to the specific database you want to check.
2. Right-click on “Security” and select “Roles“.
3. This will show you a list of all the roles defined within that database.

Another way to check database roles with a system view:

1. Open a new query window in SSMS.
2. Use the below query to check all ‘Database_Role.’

SELECT NAME 
FROM sys.database_principals
WHERE type_desc = 'DATABASE_ROLE';

Roles Key Differences Brief

When to Use Which Role

• SQL Server Roles: To manage overall user access to the SQL Server instance and its resources.
• Database Roles: To grant granular permissions within specific databases based on user needs.

Best Practices for Using SQL Server and Database Roles

Follow these tips to keep things safe and organized when setting up who can access what in SQL Server:

• Limit Sharing: Only give roles what they need. Don’t give extra access.
• Keep Checking: As things change, update roles so access stays right.
• Give Just Enough: Roles and users should only have what they need to do their job.
• Make Your Own Roles: Don’t use predefined roles. Create ones that fit your needs.
• Roles for Jobs: Use roles for different jobs to keep things organized.
• Write it Down: Keep track of all the roles, so you don’t get confused.
• Double Check: Look at the roles regularly to make sure everything is safe.

Conclusion

Understanding the difference between SQL Server roles and database roles is important to keep your SQL Server secure. SQL Server roles provide server-wide control, while database roles offer more controlled permissions within specific databases. By leveraging these roles appropriately, database administrators and SQL developers can enhance security, streamline permission management, and ensure users have the necessary access without compromising security.

Next Steps

• Check out these MSSQLTips.com Security tips.