Cannot make a role a member of itself error in SQL Server

By: K. Brian Kelley   |   Updated: 2010-08-30   |   Comments (2)   |   Related: > Security

Problem

I am attempting to nest roles in creating my database security model, but I'm getting an error when I use sp_addrolemember. The error is this:

Msg 15413, Level 11, State 1, Procedure sp_addrolemember, Line 92
Cannot make a role a member of itself.

I'm not making a role a member of itself, so I don't understand this error. What causes it?

Solution

What is the likely cause, is you have several levels of nested roles and you're actually creating a circular reference with those nested roles. This is easier to illustrate with an example:

USE MSSQLTips;
GO

CREATE ROLE Level1;
GO

CREATE ROLE Level2;
GO

CREATE ROLE Level3;
GO

EXEC sp_addrolemember @rolename = 'Level1', @membername = 'Level2';
GO

EXEC sp_addrolemember @rolename = 'Level2', @membername = 'Level3';
GO 
-- Execute all the commands above this line and you shouldn't have any errors 
-- (assuming the MSSQLTips database exists).

-- This statement will fail with the error because Level3 is ultimately a member 
-- of Level1. Therefore,the statement effectively makes Level1 a member of itself. 
-- That's why SQL Server rejects it.

EXEC sp_addrolemember @rolename = 'Level3', @membername = 'Level1';
GO

When we try to make Level1 a member of Level3, we're attempting to create a circular reference. Level3 is a member of Level2, which is a member of Level1. Therefore, Level1 can't be a member of Level3. Otherwise, it would be a member of itself and that's why SQL Server throws the error. The stored procedure sp_addrolemember makes a call to an undocumented function is_rolemember() to check for circular references. If one is found, an error is thrown and the T-SQL command does not complete.

So how do you avoid this situation? Actually, this is a scenario that comes up on the Windows side all too often, especially when there is a need for granular permissions. When there are many, many groups, most of the methods simply become unwieldy. But during an initial Active Directory deployment, it's a good idea to document what the various groups should be, what permissions they should have, and what real-world department or job role is the security group modeled after. The same thing can be done for database roles:

1. Determine what the different classifications are for security (e.g. user versus technician versus manager).
2. Deletermine what permission each classification should have.
3. Determine if it makes sense to nest any of the classifications.
4. Verify that there's not a circular reference being formed.
5. Implement in the database and test.

In other words, a bit of proper planning is usually good enough to avoid these problems with most databases. Any security changes should be meticulous and well-thought out anyway, so the process of documenting the security model and verifying it will ensure that the process slows down enough, so that most mistakes will be caught.

Next Steps

• Read these additional tips related to SQL Server 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