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

By:   |   Comments (2)   |   Related: > Security


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?


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:





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

EXEC sp_addrolemember @rolename = 'Level2', @membername = 'Level3';
-- 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';

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Monday, August 30, 2010 - 10:32:14 AM - K. Brian Kelley Back To Top (10092)
Intentionally, no one. However, with a large and complex security model, it is possible for this scenario to happen. That's why tools which audit Active Directory, like SomarSoft's DumpSec have an option to check to see if you've got a loop in your groups and to stop processing when that is detected. While it is far less likely in SQL Server because very often we're not building anywhere near as complex a system as we do in large environments in Active Directory, I have seen third party applications which give me pause. And if you're trying to do very granular security, which Active Directory is not built for, it's entirely possible this gets modeled in SQL Server and then you have this scenario.

Monday, August 30, 2010 - 9:07:04 AM - Santhosh Back To Top (10091)
What nonsense is this? Who in the world would like to make a role member of its own? There is no scenario in this world which would require this kind of access. Totally ridiculous.

get free sql tips
agree to terms