Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Nesting Database Roles in SQL Server


By:   |   Last Updated: 2010-04-07   |   Comments   |   Related Tips: More > Security

Problem

I know in SQL Server you can nest user-defined database roles within the database, but is that a good idea? Do they work the same as Windows groups when they nest? What about how they interact with the SQL Server provided fixed-database roles?  In this tip I answer these questions related to nesting user-defined database roles.

Solution

SQL Server's user-defined database roles (now also called flexible database roles) function in much the same way as Windows security groups. In Windows security, a nested group has the permissions assigned directly to it as well as the permissions of the group it is a member of. The same is true for user-defined database roles within SQL Server. A user-defined role that is nested in another role has the permissions assigned directly to it as well as to the role it's a member of. Nesting roles properly can often ease the application of the security model if the nesting is kept fairly shallow, if the nesting is logical, and if the roles are logically named. If these are all true, then nesting of roles is a good idea. This is easily seen with a quick example.

First, let's create two users and two roles. We use the example of a help desk setup. A help desk analyst can see the tickets table and insert into the tickets table (to open a ticket) but can't update or delete a ticket (if a ticket is to be closed, it's sent to the manager). The manager can specifically update and delete a ticket. We'll also nest the manager role in the analyst role, so it'll have select and insert rights as well.

USE MSSQLTips;
GO 

CREATE USER Manager WITHOUT LOGIN;
GO 

CREATE USER Analyst WITHOUT LOGIN;
GO 

CREATE ROLE HelpDeskAnalyst;
GO

CREATE ROLE HelpDeskManager;
GO

EXEC sp_addrolemember @rolename 'HelpDeskAnalyst'@membername 'Analyst';
EXEC sp_addrolemember @rolename 'HelpDeskAnalyst'@membername 'HelpDeskManager';
EXEC sp_addrolemember @rolename 'HelpDeskManager'@membername 'Manager';
GO 

CREATE TABLE dbo.Tickets (
  
TicketID INT IDENTITY(1,1),
  
TicketSubmission SMALLDATETIME DEFAULT GETDATE(),
  
TicketStatus CHAR(1DEFAULT 'O'
);
GO

GRANT SELECTINSERT ON OBJECT::dbo.Tickets TO HelpDeskAnalyst;
GO

GRANT UPDATEDELETE ON OBJECT::dbo.Tickets TO HelpDeskManager;
GO

Now that the setup is complete, let's see a working example:

USE MSSQLTips;
GO

-- This will work
EXECUTE AS USER 'Analyst';
GO

INSERT INTO dbo.Tickets DEFAULT VALUES;
GO

REVERT
;
GO 

-- As will this
EXECUTE AS USER 'Analyst';
GO

SELECT TicketIDTicketSubmissionTicketStatus FROM dbo.Tickets;
GO

REVERT
;
GO 

-- But this won't
EXECUTE AS USER 'Analyst';
GO

DELETE FROM dbo.Tickets;
GO

REVERT
;
GO 

-- Let's run the same queries as the manager.
-- The manager's role is nested with the analyst's role.
-- Therefore, the INSERT and SELECT query should work.

-- This will work through the nested role.
EXECUTE AS USER 'Manager';
GO

INSERT INTO dbo.Tickets DEFAULT VALUES;
GO

REVERT
;
GO 

-- As will this, through the nested role
EXECUTE AS USER 'Manager';
GO

SELECT TicketIDTicketSubmissionTicketStatus FROM dbo.Tickets;
GO

REVERT
;
GO 

-- This will also work because a manager can delete from the Tickets table.
EXECUTE AS USER 'Manager';
GO

DELETE FROM dbo.Tickets;
GO

REVERT
;
GO 

And we see that the nested roles work just fine. The manager is able to do everything granted to its role as well as the analyst role.

Now with respect to fixed database roles, here are the nesting rules:

  • Fixed database roles cannot be made a member of another database role, whether fixed or user-defined.
  • User-defined database roles can be made a member of a fixed database role.

With that said, the recommendation in Books Online is not to put user-defined database roles into fixed database roles. The warning is that you may encounter unintended security consequences. But this is true if you nest groups too much or in haphazard ways. Ideally, though, given the power of the fixed database roles, you would just want to see users (such as a Windows group mapped into the database as a user) as members of the fixed database roles.

Next Steps


Last Updated: 2010-04-07


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools