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?
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?