December 2, 2008 at 4:53 am
Hi
I have a storedprocedures which need to to execute some dynamic SQL via sp_executesql.
The only access a 'normal' user has to my storedprocedures is via GRANT EXECUTE on a SHEMA, where all my public facing StoredProcedures are in that SCHEMA.
If the 'normal' user were my own windows account then it works fine becasue I happen to be an administrator - so xp_cmdshell is working in principal, but a normal user who is not an administrator gets a, error message about not having INSERT, SELECT etc privileges on table set.
To solve this I created a new USER in my Database WITHOUT LOGIN (as I like the idea that I can give the SP more rights without exposing a Login to the outside) and updated the SP to use WITH EXECUTE AS 'MyLoginlessUser'.
Whilst this Loginless user works for all my SELECT, INSERT etc, it gives the following error relating to its use of xp_cmdshell:
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
So - my question is, can I and if so how - can I grant execute rights on xp_cmdshell for a loginless user?
Thanks
December 2, 2008 at 5:02 am
Stuart Wells (12/2/2008)
HiI have a storedprocedures which need to to execute some dynamic SQL via sp_executesql.
The only access a 'normal' user has to my storedprocedures is via GRANT EXECUTE on a SHEMA, where all my public facing StoredProcedures are in that SCHEMA.
If the 'normal' user were my own windows account then it works fine becasue I happen to be an administrator - so xp_cmdshell is working in principal, but a normal user who is not an administrator gets a, error message about not having INSERT, SELECT etc privileges on table set.
To solve this I created a new USER in my Database WITHOUT LOGIN (as I like the idea that I can give the SP more rights without exposing a Login to the outside) and updated the SP to use WITH EXECUTE AS 'MyLoginlessUser'.
Whilst this Loginless user works for all my SELECT, INSERT etc, it gives the following error relating to its use of xp_cmdshell:
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
So - my question is, can I and if so how - can I grant execute rights on xp_cmdshell for a loginless user?
Thanks
see the below link may help to you: http://msdn.microsoft.com/en-us/library/ms175046.aspx
December 2, 2008 at 5:08 am
a simpler option would be this : WITH EXECUTE AS dbo... this solves most of the problems especially with dynamic sql (or you'd have to give the rights to the base tables, or views to all the users).
However why do you need to use xp_cmdshell in the first place? This is not something that I see required by normal tasks. The only time I need this is for some admin tasks... where only me or 1-2 super users can be granted access.
What exactly do you have to accomplish?
December 2, 2008 at 5:15 am
Hi
There are 3 SPs that are public facing via a Service and they need to use BCP to create fmt and dat files, or consume these files. The consumption should be OK as xp_cmdshell isn't needed, but the only way I know to create format and data files using BCP within an SP is via xp_cmdshell?
If you have a non - xp_cmdshell way of achieving this that would be a good alternative?
The previous reponses link to the BOL article suggests using sp_xp_cmdshell_proxy_account, but this requires me to give permissions to a Windows account (which I'd rathert not do) and requires me to know what the password is - which I couldn't know.
My loginless user is place in the role db_owner, so it has the same rights as the SP has via permission chaining - except execute on xp_cmdshell seems to be linked to the windows account and not the role/user dbo.
December 2, 2008 at 5:35 am
I'll let the gurus in the matter answer that one... my knowledge of bcp stops at spelling it ;).
Is this a valid option : create an ssis package that does whatever needs to be done. Then create a job to run it, then make a sp that starts the job (or the package directly). Maybe that's a path where less permissions are required (I don't know for sure, I'm just thinking out loud).
December 31, 2008 at 10:42 am
Hi kishore,
thanks alot the link you posted helped me alot.
December 31, 2008 at 12:06 pm
Ninja's_RGR'us (12/2/2008)
I'll let the gurus in the matter answer that one... my knowledge of bcp stops at spelling it ;).Is this a valid option : create an ssis package that does whatever needs to be done. Then create a job to run it, then make a sp that starts the job (or the package directly). Maybe that's a path where less permissions are required (I don't know for sure, I'm just thinking out loud).
Agreed. The downside or security hole you create by giving a Login-less account access to xpcmdshell (and essentially a lot of possibilities to do something destructive to the server from the command line), just odn't stack up as worth it in my mind.
SSIS sounds like a much better option IMO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 1:08 pm
I do not believe that there is any direct way to do this (effectively GRANT EXECUTE on xp_cmdshell). The problem is as follows:
First, the ability to use xp_cmdshell is controlled at the Service/Instance level and has three basic usage states:
NONE No one can use XP_CMDSHELL
ADMIN Only sysadmin Logins can use XP_CMDSHELL
PROXY If the proxy account is setup then everyone can use it, but non sysadmin Logins will use the proxy windows account
Secondly, XP_CMDSHELL enforces the sysadmin & proxy users requirements of the above settings by checking the sessions original Logon context, so owner chains, EXECUTE AS, etc., do NOT affect this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 1:20 pm
That said, I do however know of three indirect ways to effectively do this. The first is through SSIS as Ninja has already mentioned.
The second is to create a SQL Agent Job with an Operating system(CmdExec) step to do the execution.
The third is to Setup a Service Broker Queue whose activation procedure will receive the request for the command to be executed, validate it, execute it using XP_CMDSHELL and then return the results. This assumes that your server allows admin-only use of XP_CMDSHELL, that the activation procedure is running under a user associated with a sysadmin Logon and that the database where it runs is TRUSTWORTHY. In this scenario, you grant permission to use this facility by GRANT-ing SEND permission on the Service Broker Service attached to the queue, to the user.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply