how do I give permissions for xp_cmdshell? 1

[jsteph]

I'm trying to use xp_cmdshell, but I get the error that the userid does not have permissions.

So I try to grant Execute on that. Well, it says I can only grant for objects in the current db. Ok, I change to Master db. Now I try to grant on the user, but now it says that user isn't found--because I'M IN ANOTHER DATABASE NOW.

Ok, just a bit of frustration over this catch-22. How do I grant permissions for a user--from a database that is NOT the master database--to use xp_cmdshell? Also, I can't even see xp_cmdshell in the list of Sp's in master--why is that? Thanks,
--jsteph

 

[NathanGriffiths]

By default only members of the SysAdmin role can grant permissions for xp_cmdshell. Doesn't sound like you have SysAdmin rights so you will have to get someone who does to grant you permissions for xp_cmdshell.



Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[jsteph]

Nathan,
This is a development machine sitting under my desk, upon which I am the administrator, and logged on as so. I installed sql server, I created the db, and as far as I know, I am the Lord God Master of Time, Space, and Dimension for everything on this machine.

I am in EM(as local), looking at the databases (including Master) and in the stored procedures lists I can't even see xp_cmdshell in any database. However, if I run the Grant command in query analyzer, it seems to know there is a procedure with that name somewhere.

When run from the database I created:

grant execute on master..xp_cmdshell to driveryard
---Returns:
Server: Msg 4610, Level 16, State 1, Line 1
You can only grant or revoke permissions on objects in the current database.

When run from Master:

grant execute on xp_cmdshell to driveryard
---Returns:
Server: Msg 4604, Level 16, State 1, Line 1
There is no such user or group 'driveryard'.

'driveryard' is a Login in for the server itself, and is added as a User for the database I created.
Am I missing a step or do I have incorrect syntax here?
Thanks,
jsteph

 

[jsteph]

...Ok, my temporary blindness--I didn't look in 'Extended store procedures'...so I found xp_cmdshell.

I right clicked, and gave permissions to Public, and now the procedure runs but I get:

xpsql.cpp: Error 87 from GetProxyAccount on line 604

I'm guessing this has to do with something external to the sp? Ie., this is a DTSRun call, and I'm creating a simple text export--is is possible that now the issue is permissions on wherever the file is to be created?
Thanks,
jsteph

 

[NathanGriffiths]

This might be to do with your SQL Server Agent proxy account settings - in Enterprise Manager, right click on the SQL Server Agent node under Management and bring up the Properties.
On the Job System tab uncheck the 'Only members of SysAdmin role can execute CmdExec and ActiveScripting job steps' box then enter a proxy network account the agent can use to run the job.

You also need to check that your SQL Server service is running under an account that is at least a Local Administrator on the machine (right click on server, choose Properties and check Security tab for 'Start and run SQL Server under this account' settings).

Also check the 'driveryard' login and ensure it is marked as SysAdmin server role.

HTH



Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[jsteph]

Nathan,
That did the trick! Thanks,
--jsteph