June 17, 2015 at 2:04 pm
For reasons beyond the scope of my question, is there a way to run this command within a Stored Procedure from a low privileged user login? I can grant the entity "db_ddladmin" privilege and the proc runs, but I'd rather not give out that level of permission to what is basically a glorified web access login. Any thoughts on this one?
June 17, 2015 at 2:31 pm
You could try this:
1) create a separate "power user" that has ddladmin authority in that db
2) create a stored proc that runs under the power user account (EXEC AS 'power_user') that has the DBCC command in it
3) grant the web user authority to exec that proc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 17, 2015 at 4:53 pm
That might work. I'll try this when I get back to work tomorrow. Thanks!
June 18, 2015 at 6:37 am
Ok, tried this approach and Viola!
However, in speaking with the dev, we are going to together attempt a different approach to solve the underlying problem that will not require such tactics as this. Although this works, and in a development environment it is a fine thing to do I feel that it is a risk (minimal, but still) on a production platform. Thanks for the answer I was given and it does work, if anyone else has to go this route.
June 18, 2015 at 9:26 am
With this method, the web login/user can use only procs that you're explicitly given the web app authority to execute. I don't know how to lessen the risk. Some user at some point must have authority to run the DBCC command.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 18, 2015 at 11:23 am
ScottPletcher (6/18/2015)
With this method, the web login/user can use only procs that you're explicitly given the web app authority to execute. I don't know how to lessen the risk. Some user at some point must have authority to run the DBCC command.
I understand that. We are going to look at ways to accomplish their goal that might not need to use a DBCC command is all. Once I understand their aim, maybe I can get together a different approach that gives them what they need. Maybe not. We'll see, but in the meantime, this IS a viable and working solution if it cannot be any other way.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply