Can you grant SA privs to a stored proc?
-
Yeah, I know... leave it to me to come up with the tough ones
As an "Acting DBA" with "SA" privs, I have some stored procs that just happen to use the "forbidden" xp_CmdShell. I'd like to be able to have some "night operators" run those procs without granting those operators "SA" privs. Don't know if it's possible but if you know how to do it, I sure would appreciate you posting how to do it.
And, no, this isn't a homework or interview question
I really need to know how to do it 
Thanks, folks.
--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 -
Yes it is possible. Give the operators execute permission on xp_cmdshell and in the OS level for the account starting the sql server give act as a part of operating system privilege. This will allow non-sysadmins to use the xp_cmdshell procedure.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thanks, Sugesh...
Wow... not sure I want to grant exec privs to xp_CmdShell... almost as bad as giving them "SA" privs in the damage they could do.
Ok... let's restate the problem... I want the operators to be able to run a proc that uses xp_CmdShell without the operators either having "SA" privs or exec privs on xp_CmdShell themselves... is there a proxy or something I could use to achieve this?
Thanks again for any ideas...
--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 -
Oh shoot... why didn't I think of this before?
I can setup a "job" to run as a user that has "SA" privs and give the operators privs to run the job! That should do it...Must be Alzheimers setting in or something...
--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 -
Did you check for the SET AS USER command din't that help you anyways. Anyways giving xp_cmdshell execute permission is quite dangerous on looking at the operations that the command can perform.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thanks again... I'll check that SET command. Jobs also look promising if I set up a "proxy" account (lucky guess on my previous post). Here's what BOL says about running jobs...
When sp_start_job is invoked by a user who is a member of the sysadmin fixed server role, sp_start_job will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin fixed server role, sp_start_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_start_job will fail.
--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 -
Just to let you know, none of the approaches suggested will work!
You can grant execute on xp_cmdshell but the internal code of xp_cmdshell will return an error if the invoker does not have the sysadmin role.
Similarly for sp_start_job, you can grant execute rights but when executing the sp, if the job has a different owner, the sp internal code checks for the sysadmin role.
SQL = Scarcely Qualifies as a Language
-
Thanks Carl,
Yeap... the thing about the proxy needing to be available is pretty much what books online said... thanks.
--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 -
Do not know if this approach would work for your requirments, but you use DTS.
1. Install the DTS run time where the "night operators" have access.
2. Create a DTS package, save as a .dts file on the "night operators" machine.
3. Create a cmd file to run the DTS on the "night operators" machine.
Alternatively:
1. The "night operators" update a control table to set a column to true
2. A job runs frequently and when the column is true, executes the sp and then sets the column to false.
Good Luck
SQL = Scarcely Qualifies as a Language
-
All good ideas... Thanks, Carl!
--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 -
I think you mean SETUSER. SETUSER requires sysadmin rights to use.
Unfortunately, in SQL Server 2000 there's no way to handle this execution context switch.
Another solution, if the command prompt side of the stored procedure can be broken out, you could build that as a CmdExec job step. As long as the SQL Server proxy account is configured, if they can execute the job it should be able to fire off.
If it can't go with the job that monitors a table solution, as it's really the only alternative.
K. Brian Kelley
@kbriankelley -
I have used the proxy_account in the pass successfully and by limiting the windows rights that the account enjoys I don't see problems in using xp_cmdshell.
In 2005 there is an even cooler way: "EXCEUTE AS"
* Noel -
Yup, EXECUTE AS rocks.
K. Brian Kelley
@kbriankelley -
I think the trick is to use a job, but not let the user start the job directly - avoids the proxy account issues.
-
You mean just use a scheduled job? Or is there some way to allow the user to start the job "indirectly"?
--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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply