{"id":90717,"date":"2021-04-26T19:02:36","date_gmt":"2021-04-26T19:02:36","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90717"},"modified":"2021-04-29T15:29:51","modified_gmt":"2021-04-29T15:29:51","slug":"sql-server-security-providing-a-security-model-using-user-defined-roles","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/security\/sql-server-security-providing-a-security-model-using-user-defined-roles\/","title":{"rendered":"SQL Server security \u2013 Providing a security model using user-defined roles"},"content":{"rendered":"

When developing an application that accesses SQL Server, you need to set up a security model, so each user gets the access they need based on their job duties. Some applications require the same access for all users, while other application might require different security access rights based on the role a user has in the organization. The best practice for providing user access is to use role-based security. SQL Server provides user-defined Server and Database roles for defining security rules for different user security needs. This article will discuss how to use user-defined server and database roles to provide secure access to SQL Server resources.<\/p>\n

What is Role-Based Security?<\/h2>\n

Role-based security is the concept of providing each application user access to the SQL Server resources they need by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contains members. When a user is a member of a role, they inherit the permissions associated with the role.<\/p>\n

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

Since SQL Server 2012, Microsoft has provided two different types of user-defined roles: Server and Database. The user-defined server roles provide security access to server resources. In contrast, user-defined database roles provide access to database resources.<\/p>\n

User-Defined Server Roles<\/h2>\n

User-defined server roles are created at the server level and allow you to assign a specific set of server-level permissions to the role. One example of where a user-defined server role would be useful is to limit the server-level access that a junior DBA might have. By creating a server-level role, you could provide a junior DBA with access to only those server resources they need to perform their Junior DBA duties without giving them full server access via the sysadmin fixed server role.<\/p>\n

To demonstrate how to create a server-level user-defined role with limited server permissions, I will create a user-defined server role named Junior DBA<\/em>. As the name implies, this user-defined role will be set up with a limited set of server rights for a junior DBA. I want this Junior DBA<\/em> user-defined role to only have access to view databases and server state information.<\/p>\n

The first step to setting up the Junior DBA<\/em> user-defined role is to create the role. The new role can be created using TSQL or SSMS. Listing 1 shows the TSQL code to create this role.<\/p>\n

Listing 1: Creating a user-defined server role<\/strong><\/p>\n

CREATE SERVER ROLE [Junior DBA] AUTHORIZATION SA;<\/pre>\n
The code in Listing 1 only creates the role and gives the ownership of this role to the account SA<\/em>, by providing the AUTHORIZATION<\/code> keyword followed by the login SA<\/code>. If the authorization keyword and login were omitted, then my login would have owned this user-defined server role. This TSQL code has only created the role but did not assign any server rights to the new role.<\/p>\n
I could have also created this server role using SSMS by performing the following steps.<\/p>\n
    \n
  1. Open up Object Explorer<\/em> and connect to the server where the new server role needs to be created.<\/li>\n
  2. Expand the Security<\/em> folder.<\/li>\n
  3. Right-click on the Server Role<\/em> item and select the New Server Role\u2026<\/em> item from the pop-up menu.<\/li>\n<\/ol>\n
    If I had used the SSMS steps to create my Junior DBA<\/em> role, then the New Server Role<\/em> window in Figure 1 would have been displayed.<\/p>\n
    \"\"<\/p>\n
    Figure 1: New Server Role window<\/strong><\/p>\n
    As you can see, SSMS automatically generated a user-defined role name of ServerRole-20210403-063456<\/em>. Since this isn\u2019t the name I want for my role, I now need to type over that automatically generated name with my role name of Junior DBA<\/em> and enter dbo<\/em> in the owner field. At this point, I could click on the OK<\/em> button, and this role would be created, just as if I ran the TSQL code in Listing 1. But since I want my new Junior DBA<\/em> role to have some rights, let me move on and show you how to assign rights to this new role using SSMS.<\/p>\n
    In Figure 1, the Securables<\/em> section on the right side of the window has a list of server-level rights that can be granted to a user-defined role. For this demonstration, I\u2019m only going to give my junior DBA role the following rights: view any database, view any definition, and view server state information. To give these rights, I first click on the Servers<\/em> item and then scroll down and grant permissions to those view rights I want my junior DBA to have. After doing this, my New Server Role window looks like what is shown in Figure 2.<\/p>\n
     <\/p>\n
    <\/p>\n
    Figure 2: Junior DBA user-defined role with permission<\/strong><\/p>\n
    The last step needed to create my new Junior DBA<\/em> role with these rights is to click on the OK<\/em> button.<\/p>\n
    The rights I provided to my new junior DBA role using SSMS could have also been granted using TSQL by running the script in Listing 2.<\/p>\n
    Listing 2: TSQL to grant permissions to the Junior DBA role<\/strong><\/p>\n
    USE [master];\r\nGO\r\nGRANT VIEW ANY DATABASE TO [Junior DBA];\r\nGRANT VIEW ANY DEFINITION TO [Junior DBA];\r\nGRANT VIEW SERVER STATE TO [Junior DBA];\r\nGO<\/pre>\n
    At this point, the new user-defined server role Junior DBA<\/em> role has only been created and doesn\u2019t contain any members. Before I can add members, I need to create a login for my junior DBA. For this demo, I will create a SQL authenticated login named JD<\/em> using the TSQL code in Listing 3.<\/p>\n
    Listing 3: Creating my JD login<\/strong><\/p>\n
    USE [master]\r\nGO\r\nCREATE LOGIN [JD] WITH PASSWORD=N'Junior', \r\n                       DEFAULT_DATABASE=[master], \r\n                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF\r\nGO<\/pre>\n
    To make the JD<\/em> login a member of the Junior DBA<\/em> role, I could use SSMS to make it a member of this role from the properties of the login or the role. I can also use the TSQL code in Listing 4.<\/p>\n
    Listing 4: Adding member to a new server role<\/strong><\/p>\n
    ALTER SERVER ROLE [Junior DBA] ADD MEMBER [JD]<\/pre>\n
    I have not seen server roles used that much in the real world, but user-defined server roles provide value when you want to create a role that has limited server-level permissions.<\/p>\n
    User-Defined Database Roles<\/h2>\n
    User-defined database roles are for setting up different security profiles at the database level. For example, suppose you have an application with different kinds of security needs based on each user’s role in the organization. By using user-defined roles, you could set up a different role for each of the different security profiles needed by your application.<\/p>\n
    To show how user-defined database roles can provide database users access to database resources, I will add three different database users to the AdventureWorks2019<\/code> database. Each user has a different set of access rights to support a hypothetical sales application. The SalesForce application requires 3 different security profiles to support the security model needed. Each security profile will have a different set of security permissions. For each security profile, I will set up a different user-defined role and then assign to each new role the required permissions. The user-defined roles will be called: SalesManager<\/em>, ProductionControl<\/em>, and SalesPerson<\/em>.<\/p>\n
    The SalesManager<\/em> user-defined role will need access to control all resources in the AdventureWorks2019 <\/code>database. The ProductionControl<\/em> user-defined role will need to have rights to insert, update, and deleted data in any tables in the Production<\/code> schema and will need read access to all other tables in the AdventureWorks2019<\/code> database. The last profile, SalesPerson<\/em>, will need access to insert, update and delete information in all tables in the Sales<\/code> schema tables and have read access to all other tables in the AdventureWorks2019<\/code> database.<\/p>\n
    In order to show how to add databases users to each of these different user-defined roles, I will create the following logins and AdventureWorks2019<\/code> database users: Tom<\/em>, Dick<\/em>, and Sally<\/em>. These logins and users will be created using the TSQL Code in Listing 5.<\/p>\n
    Listing 5: Creating Logins and Database Users<\/strong><\/p>\n
    USE [master]\r\nGO\r\n-- Create Logins\r\nCREATE LOGIN [Tom] WITH PASSWORD=N'Salesman', \r\n                       DEFAULT_DATABASE=[AdventureWorks2019], \r\n                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;\r\nCREATE LOGIN [Dick] WITH PASSWORD=N'ProductionControl', \r\n                       DEFAULT_DATABASE=[AdventureWorks2019], \r\n                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;\r\nCREATE LOGIN [Sally] WITH PASSWORD=N'SalesManager', \r\n                       DEFAULT_DATABASE=[AdventureWorks2019], \r\n                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;\r\nGO\r\nUSE AdventureWOrks2019;\r\nGO\r\n-- Create Database Users\r\nCREATE USER [Tom] FOR LOGIN [Tom] WITH DEFAULT_SCHEMA=[dbo];\r\nGO\r\nCREATE USER [Dick] FOR LOGIN [Dick] WITH DEFAULT_SCHEMA=[dbo];\r\nGO\r\nCREATE USER [Sally] FOR LOGIN [Sally] WITH DEFAULT_SCHEMA=[dbo];\r\nGO<\/pre>\n
    Since the SalesManager<\/em> role will need access to all resources in the database, they will be setup with db_owner<\/em> permissions. To create the SalesManager<\/em> role and grant it permissions, I will perform the following steps using SSMS:<\/p>\n
      \n
    1. Open up Object Explorer<\/em><\/li>\n
    2. Expand the AdventureWorks2019<\/em> database<\/li>\n
    3. Expand the Security<\/em> folder<\/li>\n
    4. Right-click on the Role<\/em> item and then mouse over the New<\/em> item in the menu displayed to bring up the next menu, where I then click on the New Database Role\u2026<\/em> menu item.<\/li>\n<\/ol>\n
      After completing these steps, the Database Role \u2013 New<\/em> window in Figure 3 will be displayed.<\/p>\n
      <\/p>\n
      Figure 3: New Database Role Window<\/strong><\/p>\n
      With the General<\/em> tab selected on the left, I will enter SalesManager<\/em> in the Role<\/em> name<\/em> field on the right. Since I want dbo<\/em> to own this new role, I enter dbo<\/em> in the owner field. To add members to this role, I click on the Add<\/em> button. Doing this brings up the Select Database User or Role<\/em> window, where I will then use the Browse<\/em> button to select Sally<\/em> to be added as a member to this new role, as shown in Figure 4.<\/p>\n
      <\/p>\n
      Figure 4: Adding Sally as a member<\/strong><\/p>\n
      To finish adding Sally as a member to this new role definition, I click on the OK<\/em> button. Upon doing this, the window in Figure 5 is displayed.<\/p>\n
      <\/p>\n
      Figure 5: New Database Role with Sally as a member<\/strong><\/p>\n
      To finish creating this role, I click on the OK<\/em> button. At this point, all I\u2019ve done is create a new role named SalesManager<\/em> that has Sally<\/em> as the sole member of this role. Alternatively, I could have used the script in Listing 6 to create the SalesManager<\/em> role.<\/p>\n
      Listing 6: Creating SalesManager role with Sally as a member<\/strong><\/p>\n
      USE [AdventureWorks2019];\r\nGO\r\nCREATE ROLE [SalesManager] AUTHORIZATION [dbo];\r\nGO\r\nALTER ROLE [SalesManager ADD MEMBER [Sally];\r\nGO<\/pre>\n
      To finish setting up this role, I will need to grant permissions. Since the SalesManager<\/em> needs to be able to manage all resources in the database, I will give this role the same permissions as the fixed database role db_owner<\/em>. The easiest way to accomplish this is to make the SalesManager<\/em> role a member of the db_owner<\/em> fixed database role, which is accomplished by running the code in Listing 7.<\/p>\n
      Listing 7: Adding SalesManger roles as a member of the db_owner role<\/strong><\/p>\n
      USE [AdventureWorks2019]\r\nGO\r\nALTER ROLE [db_owner] ADD MEMBER [SalesManager]\r\nGO<\/pre>\n
      The next security profile to set up for my hypothetical application is ProductionControl<\/em>. User Dick<\/em> will be a member of this role, and the role needs to have SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code> rights to all tables in the Production Schema, as well as need read access to all tables in the AdventureWorks2019<\/code> database. To create this role, add Dick<\/em> as a member, and provide the necessary permissions, I will run the code in Listing 8.<\/p>\n
      Listing 8: Setting up the ProductionControl user-defined database role<\/strong><\/p>\n
      USE [AdventureWorks2019];\r\nGO\r\nCREATE ROLE [ProductionControl] AUTHORIZATION [dbo];\r\nGO\r\nALTER ROLE [ProductionControl] ADD MEMBER [Dick];\r\nGO\r\nGRANT DELETE ON SCHEMA::[Production] TO [ProductionControl];\r\nGRANT INSERT ON SCHEMA::[Production] TO [ProductionControl];\r\nGRANT SELECT ON SCHEMA::[Production] TO [ProductionControl];\r\nGRANT UPDATE ON SCHEMA::[Production] TO [ProductionControl];\r\nGO\r\nALTER ROLE [db_datareader] ADD MEMBER [ProductionControl];\r\nGO<\/pre>\n
      The final security profile I need to set up for my hypothetical application is SalesPerson<\/em>. For this security profile, I will be created a user-defined database role name SalesPerson<\/em>. I will add the database user Tom<\/em> as a member to this role. This role will need SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code> permissions to all tables in the Sales<\/code> schema and will need read access to all tables in the AdventureWorks2019<\/code> database. To create this role and all the necessary permissions, I will run the script in Listing 9.<\/p>\n
      Listing 9: Creating the SalesPerson user-defined role<\/strong><\/p>\n
      USE [AdventureWorks2019];\r\nGO\r\nCREATE ROLE [SalesPerson] AUTHORIZATION [dbo];\r\nGO\r\nALTER ROLE [SalesPerson] ADD MEMBER [Tom];\r\nGO\r\nGRANT DELETE ON SCHEMA::[Sales] TO [SalesPerson];\r\nGRANT INSERT ON SCHEMA::[Sales] TO [SalesPerson];\r\nGRANT SELECT ON SCHEMA::[Sales] TO [SalesPerson];\r\nGRANT UPDATE ON SCHEMA::[Sales] TO [SalesPerson];\r\nGO\r\nALTER ROLE [db_datareader] ADD MEMBER [SalesPerson];\r\nGO<\/pre>\n
      By using database roles for each of these different security profiles, I can now easily provide similar permissions to any new database users that might need any one of these security profiles. To give a new database user the same permissions as what either Tom<\/em>, Dick<\/em>, or Sally<\/em> have, all that would be needed is to make them a member of the appropriate user-defined role. Having the permissions to database objects in a user-defined role simplifies the amount of security work a DBA needs to do and ensures two database users have the same security permissions provided they are in the same user-defined database role.<\/p>\n
      Security Profiles Using User-Defined Roles<\/h2>\n
      User-defined roles is a great way to provide the same security setup to multiple logins or database users that need the same security requirements. With user-defined roles, you set it up once, and then each time someone needs the same rights as the role, you just make them a member of the role. When a user is a member of a role, they inherit the permission associated with the role. User-defined roles minimize the administration work while ensuring members have the same security permission. If you have an application that is used by many users and requires different security profiles for different groups of users, then user-defined roles are the best way to set up and administer these different security profiles.<\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      Greg Larsen continues his series and shows how user-defined roles roles can control SQL Server security.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143530],"tags":[5134],"coauthors":[11330],"class_list":["post-90717","post","type-post","status-publish","format-standard","hentry","category-featured","category-security","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90717","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=90717"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90717\/revisions"}],"predecessor-version":[{"id":90723,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90717\/revisions\/90723"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90717"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90717"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90717"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90717"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}