Flexible Database-Level Roles

This is following up from my previous post on Database Roles. In my previous post, I talked about fixed database roles. If you’ve forgotten, go back and re-read the article. I’ll still be here when you get back.

So, what exactly are flexible database roles? They are roles that you create in the database. It’s as simple as that.

When you start with a new database, there are no flexible roles, just the fixed roles. The upside of this is that you are free to create all of the roles that you need and grant all of the permissions that you desire to these roles.

So, where to begin? Let’s start with an empty database:

  -- Create our sample database and switch to it
  CREATE DATABASE TestFixedRoles;
  GO
  USE TestFixedRoles;
  GO

  -- Create a new user and grant them db_securityadmin
  CREATE USER user_security WITHOUT LOGIN;
  CREATE USER user_test WITHOUT LOGIN;
  GO

  EXEC sp_addrolemember N'db_securityadmin', N'user_security';
  GO

So, now we’ve created a new database, a new user, and granted db_securityadmin to our new user. You might remember from the previous article that db_securityadmin can’t grant access to fixed database roles. However, db_securityadmin has no such limitation when dealing with flexible database roles.

Now, let’s set up a test user for the purposes of this introduction to flexible database roles.

  CREATE USER test_user WITHOUT LOGIN;
  GO

This user is now a member of the PUBLIC role. What kind of access does public have?

  CREATE SCHEMA test;
  GO

  CREATE TABLE test.t1 (number INT);
  GO

  DECLARE @i INT;
  SET @i = 0;

  WHILE @i < 1000
  BEGIN
    INSERT INTO test.t1 VALUES (@i);

    SET @i = @i + 1;
  END

  EXECUTE AS USER='test_user';
  GO

  -- This will fail because we have no access to the test schema
  SELECT * FROM t1;
  GO

  REVERT
  GO

None. PUBLIC has no access, thus test_user has no access. Let’s get test_user set up with some access.

  CREATE ROLE test_role;
  GO

  -- The scope qualifier '::' is required.
  GRANT SELECT ON SCHEMA :: test TO test_role;
  GO

  EXEC sp_addrolemember N'test_role', N'test_user';
  GO

  EXECUTE AS USER='test_user';
  GO

  -- Success!
  SELECT * FROM t1;
  GO

  REVERT
  GO

There you have it! We can select from the test schema. How does this help, though, in the real world?

For starters, by creating roles and adding users to roles you can streamline managing security through the use of roles rather than having to monitor the permissions assigned to every user, login, Windows user, and Windows group.

Second, by combining roles for managing security with schemas and stored procedures, it’s possible to carefully control and define granular access to stored procedures and data.

Let’s take a look at AdventureWorks and create an example of how we might want to accomplish this.

This procedure just retrieves customers who placed an order between two dates. Pretty simple.

  CREATE PROCEDURE Sales.GetCustomersWithOrdersBetweenDates (
    @StartDate DATETIME,
    @EndDate DATETIME
  )
  WITH EXECUTE AS OWNER AS

  SELECT pc.LastName,
         pc.FirstName,
         pc.EmailAddress
    FROM Sales.SalesOrderHeader AS ssoh
         INNER JOIN Sales.SalesOrderDetail AS ssod
            ON ssoh.SalesOrderID = ssod.SalesOrderID
         INNER JOIN Sales.Customer AS sc
            ON ssoh.CustomerID = sc.CustomerID
         INNER JOIN Sales.Individual AS si
            ON sc.CustomerID = si.CustomerID
         INNER JOIN Person.Contact AS pc
            ON si.ContactID = pc.ContactID
   WHERE ssoh.OrderDate BETWEEN @StartDate AND @EndDate
   GROUP BY pc.LastName,
            pc.FirstName,
            pc.EmailAddress;
  GO

Next we’ll want to set up some roles:

  -- this is our internal sales personnel
  CREATE ROLE internal_sales_team;
  GO

  -- these are sales people in the field
  CREATE ROLE field_sales_team;
  GO

Now we’ll create users for those roles:

  CREATE USER Iris WITHOUT LOGIN;
  GO

  CREATE USER Frank WITHOUT LOGIN;
  GO

  EXEC sp_addrolemember N'internal_sales_team', N'Iris';
  EXEC sp_addrolemember N'field_sales_team', N'Frank';
  GO

  GRANT EXECUTE ON SCHEMA :: Sales TO internal_sales_team;
  GO

Now let’s test this to see how it works:

  EXECUTE AS USER = N'Iris';
  GO
  EXEC Sales.GetCustomersWithOrdersBetweenDates '20040101', '20040601';
  GO
  REVERT
  GO

  EXECUTE AS USER = N'Frank';
  GO
  EXEC Sales.GetCustomersWithOrdersBetweenDates '20040101', '20040601';
  GO
  REVERT
  GO

Iris can successfully execute the stored procedure, despite her user not having access to the Sales schema because she is a member of the internal_sales_team role which does have execute permissions on the Sales schema. However, Frank cannot execute the stored procedure since the field_sales_team role does not have access to the Sales schema. If you try to run the SQL from this stored procedure as either Frank or Iris the SQL will fail since neither user has select permissions on the Sales or Person schemas.

As I mentioned earlier, through a careful combination of users, logins, roles, Windows users, and Windows groups you can assemble a very secure, robust security infrastructure in SQL Server that can handle a variety of tasks while make your administrative life a lot easier.