By: K. Brian Kelley | Updated: 2010-08-30 | Comments (2) | Related: More > 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:
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:
- Determine what the different classifications are for security (e.g. user versus technician versus manager).
- Deletermine what permission each classification should have.
- Determine if it makes sense to nest any of the classifications.
- Verify that there's not a circular reference being formed.
- 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.
- Read these additional tips related to SQL Server Security
Last Updated: 2010-08-30
About the author
View all my tips