Can you grant SA privs to a stored proc?
-
I caught the tail of Carl's post just now, pretty much the same thing. Allow the user to flip a bit, or even insert an entire script into a queue table, then have a job that runs once a min that checks the queue and processes as SA. I've used this technique to allow developers to backup/restore certain databases on a dev server without giving them SA or other access. The more specific the table the less danger of course!
-
Well, a mix of both. Scheduled job polls a table. User has the ability to execute a stored procedure which makes a change in the table. Scheduled job notes the change, executes the process. It could even execute a secondary job. So indirectly the user is starting the job.
K. Brian Kelley
@kbriankelley -
Great idea... thanks everyone...
Carl, thanks for the original idea that everyone seems to be hitting on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Application Role??
-
Thought about that, Ronald... that, I believe, would allow the Operators to run xp_CmdShell anytime they wanted and that's not a good thing... 'course, I could be wrong especially since I'm the one asking for help
What do you mean by "Application Role" and how could it be used to run a proc that uses xp_CmdShell without the user having the privs to run xp_CmdShell?--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
The problem with an application role is that when it is set in SQL Server 2000 using sp_setapprole (SQL Server 2005 has sp_unsetapprole to revert back), you lose all identity except for the application role. Within the database your user is the application role. As a login you're generally anonymous, meaning you can only do what the guest user can do in a given database.
Since xp_cmdshell exists in the master database, that basically means either putting an app role in the master database or giving the guest user execute rights on xp_cmdshell. If you have to utilize another database, you really can't go the application role route.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply