SQL Server Performance Forum – Threads Archive
sp_makewebtask: granting EXEC permission to ASPNET
I’m running an ASP.Net web application (my personal website). I am using "sp_makewebtask" on some tables in conjunction with .Net’s CacheDependency object, such that when a table changes, and the .html file is generated, my asp application will re-run a SQL query and plop the resulting DataSet into memory (thus refreshing the cached DS whenever a table is updated). that’s cool, and it works great–if the tables are changed by a sysadmin, say via Enterprise Manager. problem: if i use a webform to update the table, i get sp_makewebtask permission errors. Probably because the ASPNET account, which has EXEC permission for my webapp’s table (via a proc), does not have EXEC permission on sp_makewebtask (which is called by the table’s trigger). how do i correct this? i have tried using sp_makewebtask’s @username parameter, with values of ‘sa’, ‘guest’ and ‘dbo’. Nothing. Then i altered the permission for for sp_makewebtask, and grated EXEC to "guest" and "public". My webform error message is now: "Only members of the sysadmin role can execute this stored procedure." …I really prefer not to grant the ASPNET account the "sysadmin" role. Is there any other more appropiate solution?thanks!
matt —
Matt Del Vecchio
http://www.semi-suave.com
Unfortunately that is a SQL Server error, and if that is what it is saying, then that is what you will need to grant to the user
"How do you expect to beat me when I am forever?"
yeah i see that in there now, its just hardcoded. if you really wanted to use it w/o sysadmin, you can copy it into a localized proc in your db…but by then i realized there had to be a better way for what i wanted to do.. so rather than use SQL Server to update a file on table change (which is slow) and then monitor that file via ASP.Net to update the cached version, i can just have my "add new row" .aspx page manually update the cache after a successful insert event.
]]>