Search This Blog

Tuesday, June 24, 2008

ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'

I once got this error while experimenting :

ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

So seems like xp_cmdshell is an object in database 'mssqlsystemresource' and schema 'sys'. mssqlsystemresource cannot be accessed without DAC ...

In the first go I could not understand much ..But then i tried to run the same query ( it was a BCP query ) using the windows domain account it was successful ..

So it was clear that this is a permission issue on the sql login that I was using ..
Then after long time searching BOL i found that we need to set the database trustworthy .

ALTER DATABASE mydb SET TRUSTWORTHY ON ...and we were comfortable ..

The other way is to create the xp_cmdshell proxy account to impersonate the non-sysadmin users to allow them to access resources outside the SQL Server.

sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

otherwise you can also use the GUI ..

Hope this helps ..

1 comment:

Maarten said...

THANK YOU!
Set Trustworthy On solved it..
Was trying to solve this issue for hours...