By: K. Brian Kelley | Comments | Related: > 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(1) DEFAULT 'O'
);
GO
GRANT SELECT, INSERT ON OBJECT::dbo.Tickets TO HelpDeskAnalyst;
GO
GRANT UPDATE, DELETE 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 TicketID, TicketSubmission, TicketStatus 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 TicketID, TicketSubmission, TicketStatus 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
- Read these additional tips related to security
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
