mssqltips logo

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

By:   |   Updated: 2010-08-30   |   Comments (2)   |   Related: More > 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


Last Updated: 2010-08-30


get scripts

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.





Monday, August 30, 2010 - 10:32:14 AM - K. Brian Kelley Back To Top
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
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.



download

























get free sql tips

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