SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

Software Development



EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Run the below command with sql login "mysqllogin"

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -S PCKodyaz\yukon –U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

 

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

 

But when I login with a windows account "MyDomain\MyUserName" and run the same batch. My windows account is a system administrator on the SQL Server 2005 instance.

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -c -S PCKodyaz\yukon -U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

The output of the sql batch was pointing of a successfull run

output

---------------------------------------------

NULL

Starting copy...

NULL

109 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 1

NULL

 

I was sure that I had configured the SQL Server 2005 weeks before and enabled the database instance to run the xp_cmdshell sql command by running the sp_configure stored procedure.

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

In fact, since as a system administrator (windows account) I could run the xp_cmdshell successfully. This was showing that xp_cmdshell was enabled.

So there had to be a problem with related to the sql login "mysqllogin"

On the master database, I run the below command

USE master;

GRANT EXECUTE on xp_cmdshell to mysqllogin

 

After the execute permission is granted to sql user mysqllogin, I ran the same t-sql command block with the user rights of mysqllogin user

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -c -S PCKodyaz\yukon -U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

This time the returned error message was as follows and noting the proxy account information for the xp_cmdshell

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

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.

 

I again logged in using my administrator windows account "MyDomain\MyUserName" and run the sp_xp_cmdshell_proxy_account stored procedure and created a proxy credential for xp_cmdshell

EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyUserName', 'myDomainPassword'

MS SQL Server 2005 uses the proxy account credentials to impersonate on behalf of the non-sysadmin users in order to access resources outside the SQL Server.

The syntax for the sp_xp_cmdshell_proxy_account system stored procedure is as

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

Here the account_name in my case 'MyDomain\MyUserName' is the Windows login which will be the proxy. And password is the password of the Windows login account.

To drop the proxy credential for xp_cmdshell, running the sp_xp_cmdshell_proxy_account system procedure without any parameter will be enough.

EXEC sp_xp_cmdshell_proxy_account NULL

After creating the xp_cmdshell proxy, I could successfully run the below sql command using my sql login mysqllogin

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB..Brands out c:\export.txt -c -U mysqllogin -P myloginpwd -S PCKodyaz\yukon'

Exec master..xp_cmdshell @str

output

----------------------------------------

NULL

Starting copy...

NULL

109 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 1

NULL

 

 

Published Thursday, November 23, 2006 3:19 PM
Filed Under:

Comments

No Comments
Anonymous comments are disabled

About eralper

SQL Server administrator and T-SQL developer including BI components Web programming with HTML, HTML5 and ASP, ASP.NET and .NET Framework developer SAP ABAP and SAPUI5 development, Web Dynpro and Smart Form Siemens HiPath Center CTI development
Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems