SQL Server: User-Defined Roles

  • Article

With the advent of SQL Server 2012 comes the ability to create user-defined server roles, a huge step forward for SQL management.

Denny Cherry

While we’ve been able to create user-defined database roles for decades to ease the process of granting database-level permissions, at the instance level there have always been only nine fixed server roles (eight if you’re using anything older than SQL Server 2000; the bulkadmin role was introduced with SQL Server 2005). Now, with SQL Server 2012, we’ll finally be able to create user-defined server roles.

For years, not being able to do this has created SQL management headaches. What do you do when you need to grant rights to multiple users or groups at the instance level and you want to keep those rights in sync? Say you wanted to grant the View System State right to a large number of users so they could view blocking information within the development servers. You’d have to grant one by one each user or domain group that needed this right.

If all logins are based on domain logins, about the only workaround you could use would be to create a domain group for all the users who needed this right (possibly needing a domain group for each server if the users don’t get the right on all the servers). You could put the users into this group, create a login mapped to the domain group, then grant that group the View Server State right on the server. However, you’d need to do this very carefully. Otherwise, you could grant login rights to users who didn’t have them before. You might even grant users rights you don’t want them to have.

Role with T/SQL

You can create a user-defined server role a number of ways, including T/SQL, the SQL Server Management Studio UI and Windows PowerShell. If I were to create a user-defined server role using T/SQL, I would use three different commands. First, the Create Server Role statement would create the user-defined server role; second, the Alter Server Role would add a user to the server role; finally, the Grant statement would grant the needed rights to the role.

These three statements are shown in the following code, which outlines creating a user-defined server role called “ViewServerState.” This specifies a user added to the role called “SomeFakeLogin”and the role is granted the View Server State right. To grant additional users this right, you simply need to add those users to the fixed server role using the Alter Server Role line:

You’d remove a user from a user-defined server role with the Alter Server Role. Instead of using the Add Member syntax, you’d use Drop Member:

When it comes time to deploy one or more user-defined server roles against multiple instances of SQL Server, you have a few different options. You probably don’t want to connect to each server one at a time in order to create the user-defined server roles. One option would be to use the SQL Server Management Studio feature. This lets you run T/SQL scripts against multiple instances.

You can also use the Windows PowerShell components of SQL Server to deploy new user-defined server roles against all instances of SQL Server within your organization. (Due to the multiple ways to use Windows PowerShell to deploy these scripts, this process is outside the scope of this article.)

SQL Server Management Studio

You can easily perform the same action with the SQL Server Management Studio UI. To create a user-defined server role, connect to the instance within the Object Explorer. Navigate to InstanceName | Security | Server Roles within the object explorer. Right-click on Server Roles and select New Server Role from the context menu. When the New Server Role window opens, specify the Server role name and Owner, then select the Securables and then the Permissions you want the role members to have (see Figure 1).

Figure 1 Specify the rights you want a particular user to have.

After completing the fields on the General page, select the Members page (see Figure 2) and specify the SQL Server Logins that will become members of this user-defined server role.

Figure 2 Selecting Members will specify which users have access to this server role.

After selecting the Role Members, select the Memberships page. This will specify the server roles of which this user-defined server role will be a member. If you specify a server role on this screen, the users within the user-defined server role will also have the rights granted by that server role.

If you were to create a user-defined role and make it a member of the serveradmin fixed server role (see Figure 3), all members of the user-defined server role would not effectively be members of the serveradmin fixed server role. As with nesting domain groups or database roles, you need to exercise great care when nesting roles so you don’t grant users rights they shouldn’t have from higher-end groups.

Figure 3 You can apply your user-defined role to other server roles.

To nest server roles using T/SQL, you’ll still have to use the Alter Server Role statement with the Add Member syntax. For example, to make the ViewServerState user-defined server role a member of the setupadmin fixed server role, you’d alter the setupadmin fixed server role. Then you’d add the ViewServerState user-defined server role as a member:

User-defined server roles have many uses. There are dozens of instance-level rights you can grant to a user-defined server role to simplify managing these rights. You could also create a junior DBA role that grants some rights, but not full systems administrator rights. You could create an AlwaysOnAdmin group, which would allow rights to failover an AlwaysOn database (which must be done from within SQL Server) without having full systems administrator rights.

There are a variety of use cases for user-defined server roles. These will greatly ease administration workloads. They’ll also help increase the security of SQL Server instances, as these new server roles will reduce the instances of accidentally granting inappropriate rights.

DennyCherry

Denny Cherry* has more than a decade of experience managing SQL Server. His areas of expertise include system architecture, performance tuning, replication and troubleshooting. He holds several certifications related to SQL Server, including the Microsoft Certified Master for SQL Server 2008. He’s been a Microsoft SQL Server MVP for several years, and has written numerous technical articles and books on SQL Server management and how SQL Server integrates with various other technologies.*

Related Content