asked
Problem with running sp_helprolemember
I have a series of SPROCs that identify if a specific user ID is authorized to conduct specific actions. There are 3 levels of users to check, full admin personnel, category admin personnel and editors. Of the 3, only those individuals considered full admin have SQL accounts and their access is based on membership in a SQL role. The other two have membership contained in one of two tables. This check is used in a web application which uses a single connection string to conduct business. We will call this account "Jane." Each SPROC calls a second "helper" SPROC that determines if the provided user ID is a member of the full admin category by dumping the results of sp_helprolemember 'superAdmin' and then checking to see if the user ID corresponds to a username found in those results. Now the problem. When the SPROC is executed as Jane on the website, the database throws and error stating the 'superAdmin' role does not exist. If I run the SPROC under my own windows identity it works (as a sysadmin). The problem, it seems, is the Public role assignment. The sp_helprolemember SPROC needs to be run by an account with Public access. Jane is assigned to the public role at server level, but in the specific database she is not, nor can I add her or anyone else for that matter. I absolutely do not want to use a securityadmin account to connect to the base SPROCs in order to execute the helper SPROC that checks for superAdmin priviledges. The only other solution I have now is to conduct the check by itself using the securityadmin account, but that causes additional work and I want all the checks to run from a single SPROC. Any suggestions?
How about trying to use the [EXECUTE AS][1] functionality so that the check procedure is run at a higher security level? I think that'll do it. [1]:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
@anjiTech : if Grant's answer helped you, please mark it as the accepted answer (the tick mark next to the answer text). Thanks.
Grant, thanks for the recommendation, will give it a shot and see what happens.
Thanks all for the very quick responses! As reported above using the WITH EXECUTE AS 'Donald' WHERE Donald is the database account that has db_securityAdmin and db_accessAdmin role assignment in the specific database. Doing this fixed the problem, appreciate the help!
Glad it was useful. And thanks for reporting back. It's good for the next person with the same question.
You don't add individual users to the public database role, you simple add them as users in the database, and by default they will be members of public. What I don't get is how 'Jane' is executing the stored procedure if she is not even a member of the database?
Kev, Jane is a member of the database and has execution rights on the SPROCs. That's the frustrating part of it all, she should be, by all rights, a member of the Public role in the database, but the system throws the error only when Jane executes it.
superAdmin is a database role. The error is 'superAdmin' role does not exist. I altered the helper SPROC with the WITH EXECUTE AS 'Donald' where Donald is the database role that has securityAdmin rights and it works.
