Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.

What is role-based security?

Role-based security is the concept of providing logins and/or database users access to a SQL Server resource by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contain members. When a login or database user is a member of a role, they inherit the role’s permissions.

When role-based security is used, the actual access permissions to SQL Server resources are granted to a role and not a specific login or user. Role-based security reduces the amount of administration work needed to grant and manage security when multiple logins or users require the same access to SQL Server resources. Once a role has been set up, and the appropriate permissions are granted to it, it is just a simple matter of adding logins or users to the role to provide them with the same access as the role. Without using roles, an administrator would need to grant the same permissions to each login or database user, thus causing additional administration work. There is also the possibility of making an error which would result in some logins and users getting the wrong set of permissions.

There are two types of fixed roles in SQL Server: Server and Database. The fixed server roles provide specific security access to server resources. In contrast, the fixed database roles provide access to database resources.

Fixed server roles

Fixed Server roles have server-wide scope. They come with a set of canned permissions tied to them. The permissions for server roles can’t be changed or extended.

There are nine different fixed server roles provided with SQL Server, which are shown in Table 1, along with a description. The information in the table is directly from the Microsoft SQL Server documentation.

Table 1: Fixed Server Roles

Each fixed server role provides a unique fixed set of permissions that can provide different kinds of access to server resources. The set of permissions associated with fixed server roles (with the exception of the public server role) cannot be modified.

The public role is a little different from all other fixed server roles in that you can grant permissions to this role. When permissions are granted to the public role, all logins with access to SQL Server will inherit the permissions of the public role. The public role is a great way to provide some default permissions to every login.

When a login, Windows account or Windows group becomes a member of one of these server roles, they inherit the rights associated with the role. If someone needs the same rights as a server role, it is much easier to make them a member of the role instead of granting them access to each of the permissions associated with a role. Additionally, when you want to grant the same server rights to multiple logins, placing each login in the same server roles makes it easy to accomplish this, ensuring they get exactly the same permissions. User-defined roles can also be added as members of a server role. I’ll leave the discussion about user-defined roles for another article.

There are a number of different stored procedures, views and functions that can be used to work with fixed server roles. If you are unsure of the permissions associated with a server role, you can use the system stored procedures sp_srvrolepermission to displays the permission assigned to a single fixed server role or all the fixed server roles. The code in Listing 1 shows two different examples of how to use this stored procedure.

Listing 1: Using sp_srvrolepermission

For a complete list of all stored procedures, commands, views and functions that work with fixed server roles, you can check out the list by using this link.

Adding a login to a fixed server role can be done using SQL Server Management Studio (SSMS) or TSQL. To use SSMS, follow these steps:

1. Connect to an instance
2. Expand the Security item
3. Expand the Server Roles item
4. Right-click on the fixed server role and then click on the properties option
5. Click on the Add button on Server Roles Properties page
6. Select the login or user-defined server role that you want to add to fixed server role
7. Click on a series of Ok buttons to complete the addition of new member to the role

Before clicking to add the member, the dialog should look like Figure 1.

Figure 1: Add a new login to a fixed server role.

Alternatively, you can use the TSQL code to add a login to a fixed server role. The code in Listing 2 adds the Red-Gate login to the sysadmin fixed server role.

Listing 2: Adding a login to the sysadmin fixed server role.

Removing logins from a fixed server role can be done as easily as you added them by using SSMS or TSQL. The code in Listing 3 shows how to remove the Red-Gate login from the sysadmin fixed server role.

Listing 3: Removing login from sysadmin fixed server role.

Fixed server roles are a great way to provide DBAs, Security Admins, and operators access to the server resources they need to perform their job duties. By using server roles, you can simplify the granting of permissions to server resources. In addition to fixed server roles, there are also fixed database roles.

Database Roles

To help manage security at the database level, SQL Server has databases roles. Just like server roles, there are two different types of database roles: fixed and user-defined. Fixed database roles are just like fixed server roles, meaning they have a specific set of permissions associated with each one that cannot be altered. Fixed database roles only provide permissions to database resources in a specific database.

If a database user is a member of a fixed database role, they inherit the permissions that have been pre-defined for the fixed database role. Each database contains the same set of fixed database roles. Table 2 contains the names and definitions for each fixed database role definition, as found in the Microsoft documentation.

Table 2: Fixed Database Roles

A few additional special fixed database roles only apply to the msdb database or SQL Database on Azure. For more information on these special roles, see Microsoft documentation here.

There is also the public database role. Just like the public server role, rights can be granted to the public database role. When rights have been granted to the public database role in a database, those rights are inherited by each database user that has been defined as a user of the database. The public role is a great way to provide the same permissions to database resources for every database user in a database.

Not all organization will use each of these roles to provide access. Most shops use the db_datareader, and db_datawriter roles. If you make a database user a member of these roles, they will be able to read and/or update any user table in the database. Not only that, but they will also be able to update and/or read any data from any new user table that might be added in the future. This could be a good thing or a bad thing. It is a good thing if you want users to automatically gain read and/or update rights to all new user tables, regardless of the table. If you ever think you might want to add one or more tables to your database that only a few database users should have access to, then avoid using these two roles to provide blanket read and/or write access to database tables. Because these two roles automatically provide access to any new user table defined in the future, some shops prohibit the use of these roles to ensure database users only have access to the tables they need to perform their job function.

Just like fixed server roles, there are a number of system stored procedures, commands, views, and functions that can be used to display and manage fixed database roles. The code in Listing 4 shows how to use one of those system stored procedures sp_dbfixedrolepermission, to display all the permissions associated with each fixed database role in the AdventureWorks2019 database, as well as how to use this stored procedure to display just the permissions associated with the single fixed database role db_datareader.

Listing 4: Displaying permissions associated with fixed database roles

For a complete list of all stored procedures, commands, views and functions used to display and manage fixed database roles you can review the documentation found here.

In order for a database user to inherit the permissions of a fixed database role, they need to be a member of a fixed database role. To determine if a databases user is a member of a database role, you can review the role membership using SSMS by following the steps:

1. Connect to an instance
2. Expand Databases
3. Expand the database for which you want to review fixed database roles
4. Expand the Security item
5. Expand the Roles item
6. Expand the Database Roles Item
7. Double click on the role in which you want to see members
8. Review the properties window display to see the members of the role

Figure 2 shows the members of the db_datareader role:

Figure 2: Review fixed database role permissions

An application might need to programmatically determine if a database user is a member of roles. The IS_MEMBER function allows you to write TSQL code to do that. Using this function would allow you to build an application that displays different menu options for different database users, depending on the database roles that the current user is a member. To programmatically determine if the currently logged-on database user is a member of db_datawriter role, in the AdventureWorks2019 database, you could use the code in Listing 5.

Listing 5: Determining if current database user is a member of a role

The IS_MEMBER function returns a 1 if the current user is a member of the db_datawriter role or 0 if the user is not a member of this role.

Predefined Server or Database Roles

Several predefined server and database roles are provided in SQL Server. These predefined roles provide members with a fixed set of permissions based on the role. Using these predefined roles makes it easy to grant logins or database users access to a predefined set of permissions by just making them a member of a server or database role. One thing to keep in mind when using fixed server and database roles is that the permissions cannot be changed or expanded. Using predefined server and databases roles are a great way to provide a set of canned access to server and/or database resources with minimal administrative effort.

Let’s block ads! (Why?)

SQL – Simple Talk