December 1, 2009 at 2:42 pm
We have some reports that use the sp_helprole procedure to list the roles and role members for our audit team. This has worked fine in 2000, and it looks like it works the same in 2005. I am seeing some strange, new behavior with this procedure in 2008, though.
When a non-admin, or non db owner, runs this procedure, they get a list of all of the built-in database roles and ONLY the roles in which they are a member. In previous versions (2000 and 2005), they would get a list of ALL database roles.
Has anybody else come across this? If so, have you found the reason and/or a way to work with it. I know I could directly query the system tables to get the roles but I was trying to use the procedures that are already there.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
December 1, 2009 at 3:11 pm
As usual, I found the answer just a few minutes after submitting this.
The change is with the metadata permissions and how those come into play when querying the catalog views. According to BOL:
In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.
This means, in 2008, you have to at least grant the "View Definition" right in order for a non-admin user to be able to run the sp_helprole procedure and actually get all of the roles in the database.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply