SQL 2008 - xp_cmdshell and privs

[JayKusch]

OK ... here are the OS and SQL server data points:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

XP_CmdShell HAS been enabled via a facet and also progremmatically via TSQL just to hammer it home.

Executing the following code in a stored procedure that is called by a SQL job:

 'EXEC Master..XP_CMDSHELL ''fsutil volume diskfree ' + Char(@Drive) + ':'''

Error FIRST returned in job step is as follows:

Executed as user: MyDomain\MyAcct. The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229)

I then tried granting the NT domain login execute right on xp_cmdshell in the Master DB.

After doing this I recieved the error of:

Executed as user: MyDomain\MyAcct. The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. [SQLSTATE 42000] (Error 15153)

The account being used for ALL of our SQL services is an NT Domain Admin account that has been granted SysAdmin rights on the server.

It is also the same account that we use to run all of our SQL jobs.

This job runs fine on all 50+ of our SQL 05 servers ... just not working on the 08 flavor.

If any more details are needed, please let me know.

Thanks All!

Thanks

J. Kusch

 

[hmckillop]

Have a look at sp_xp_cmdshell_proxy_account

The proxy account will be used whenever a non-sysadmin is using xp_cmdshell. This account should have the minimum privileges required by the operation you need to execute. If the operation itself requires high privileges, then you should carefully choose who you allow to execute xp_cmdshell

Have a look at this link it might help

http://msdn.microsoft.com/en-us/library/ms190359.aspx

Not sure if this will work though

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JayKusch]

Thanks for the info hmckillop,

I have read that KB already but as stated in my post the account IS part of the sysadmins group ...

The account being used for ALL of our SQL services is an NT Domain Admin account that has been granted SysAdmin rights on the server

I do not want to start creating proxy account if I do not have too.

I have however already begun the process of seeing if the proxy account is a temp workaround.

Thanks Again!



Thanks

J. Kusch

 

[hmckillop]

Sorry missed that bit.

"I'm living so far beyond my income that we may almost be said to be living apart