Xp_cmdshell Execute Rights (2005)

Sep 11, 2006

I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?

I solved it by checking "Control server" under "Properties" > "Securables"
for the login, but I don't actually want this login to have full control.

(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)

View 3 Replies


ADVERTISEMENT

Analog Of Xp_cmdshell With Limited Rights

Dec 15, 2004

Hi, we have a generic way to call DTS

CREATE procedure dbo.run_our_dts @Our_dts_namevarchar(8000)
as
declare@retcodeint
declare @run_sqlvarchar(8000)

select @run_sql = 'dtsrun /S '+@@servername+' /E /N '+@Our_dts_name
print @run_sql
exec @retcode=master..xp_cmdshell @run_sql

if @retcode<>0
begin
Raiserror('DTS RUN ERROR',16,1)
end

it works fine but ,Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps

we don't want to disable this setting

Does anyone now extended procedure
similar to xp_cmdshell, with abilty to
start dtsrun ?

Thank you

Alex

View 1 Replies View Related

I Need To Give DBA Full Admin Rights To SQL 2005 Without OS Windows Rights, Can Anyone Help Please!!

Jul 12, 2007

The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:



Title-Reporting services configuration manager

Error-There was an error refreshing the UI. bla bla bla

A WMI error has occurred and no additional error information is availiable



Title-Reporting services configuration manager

Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla

A WMI error has occurred and no additional error information is availiable



then when he's in sql server 2005 surface area configuation

Title-Surface Area Configuration

Error-Access denied (system.management)



Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???



Please help!!



Thanks for any time anyone has taken to review this thread!!

View 8 Replies View Related

How Can I Execute Stored Procedures With Correct User Rights?

Sep 3, 2007

Hi,

I have a problem with sp execution.:







objects of [dbo]

Tables of [nuran]

Grants of [nuran]


[dbo].tabloA

[nuran].tmptabloA

Select,insert, update on [dbo].tabloA


[dbo].tmptabloA



Deny for [dbo].tmptabloA


[dbo].sp_yordam



Grant for executing [dbo].sp_yordam









(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [tmptabloA]

COMMIT TRANSACTION @Tran1


When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].

If I write the procedure like that:

(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS

declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'

SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)

END

And it executed by user [nuran],then it used the correct table [nuran].tmptabloA

Is there any way to use user€™s table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [nuran].[tmptabloA]

COMMIT TRANSACTION @Tran1


I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?

Thanks a lot

Nuran

View 4 Replies View Related

SQL 2012 :: Access Rights To Execute Stored Procedure If There Is No DML Involved

Jul 2, 2014

I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures

I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working

Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.

View 7 Replies View Related

Minimum Set Of Permissions And User Rights To Execute A Report Definition

Jan 28, 2008

To use Reporting Services as a rendering engine I want to configure a local user on the server that has only the minimum set of permissions and user rights. The server is W2K3 SP2 and SQL 9.0.3200.

In particular, this local user has been removed from the local "Users" group and so is the "Authenticated Users" built-in group. In Reporting Services, it is mapped to a role that only has the "Execute Report Definitions" task permission.

Then, following the details in http://support.microsoft.com/kb/812614/ (Default permissions and user rights for IIS 6.0) I added all file security and local user rights required for "Users" and also granted and propagated "Read&Execute" on the "Reporting Services" folder and verified this using "Effective Permissions" on the ReportService2005.asmx file.

However, I still get 401 Unauthorized, also after a complete restart of all related machines and services.

Once I add the user or "Authenticated Users" back to "Users" everything works fine.

What permissions might I be missing? Where could I find those permission requirements documented?

I tried analyzing the 401 using auditing file and object access security but to no avail. There are no Failure audit entries in the Security log.


How can I investigate the minimum permission set?

What is the risk of leaving the user in the "Users" local group?


Any help appreciated.

View 1 Replies View Related

How To Grant Execute On Xp_cmdshell??

Apr 2, 2002

Can anyone help me please.
Healp please.
Does anybody know how to make xp cmdshell runnable for users other than Admin. It should be possible to grant execute to others, but i can't figure out how.

This is what i found:

Be aware that when you grant execute permission for xp_cmdshell to users, the users will be able to execute any operating-system command at the Windows NT command shell that the account running SQL Server (typically local system) has privilege to execute.

To restrict xp_cmdshell access to users who have administrator permission on the Windows NT-based computer where SQL Server is running, use SQL Setup or SQL Enterprise Manager to set the server options, selecting the "xp_cmdshell - Impersonates Client" option. With this option selected, only users who have connected to SQL Server via a trusted connection and are members of the local Administrators group on that computer are allowed to use xp_cmdshell. The commands run by xp_cmdshell continue to execute in the server's security context.

View 1 Replies View Related

Execute Xp_cmdshell And Other SA Storedproc

Mar 8, 2004

Hi all,

I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)

Pls tell how to do this, it is quite urgent.

View 1 Replies View Related

Permissions For Non Sa User To Execute Xp_cmdShell

Mar 6, 2008

Hi,    I want to execute BCP in Query Analyser in SQL Server 2005 Express for that i surf on net and find that i should execute BCP under xp_cmdShell, That works good for addministritative account on SQL. But i want the working will be done by a non administrative account or non 'sa' user.How can i assign a non sa User permissions to execute xp_cmdShell? or just tell me any other alternative way to run BCP in Query Analyser or code behined. thanx 

View 1 Replies View Related

Failing To Execute Command Using Xp_CmdShell

Sep 28, 2001

Hi all,

In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command,
operable program or batch file."
This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full..
waiting for reply

View 2 Replies View Related

Execute SSIS Package From SP Without Xp_cmdshell?

Nov 12, 2006

A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.

One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.

Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?

Thanks, Dave

View 1 Replies View Related

Execute SSIS Package From Xp_cmdshell?

Mar 19, 2008

How to execute SSIS package from CMD or using xp_cmdshell?
SSIS package is saved in the folder of the integration services Db.

Thanks in advance.

View 2 Replies View Related

How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly

Jul 26, 2006

Dear all,

Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.

I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:

select dbo.CLRHTMLString('abc')

The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).

To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:

EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.

Note: The 'webuser' database role has Execute permission on the Assembly.

I have also tested this from my actual web page, with the following results:
(1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly.
(2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.

Further test results:
(3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.

(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.

Thankful for any advice on this matter.

View 4 Replies View Related

Xp_cmdshell - Need To Execute External Program Asynchonous

Oct 21, 2004

hi,

i need to start an external program (vb.net) from within a trigger. via xp_cmdshell everything works fine except that the execution of the trigger waits for the vb-program to finish.

now i tried a workaround with a "starter application" which only calls the vb.net program (asynchronous) an ends.

this works fine from a dos prompt (command prompt returns immediately and vb.net task is running). but when i call the starter app from the trigger with xp_cmdshell, the behavior is the same as before: execution does not continue until the vb.net task ends.

any other ideas that could solve my problem? submitting an agent-job is not fine for me because i need to supply parameters to the called program and maybe i need call it more than once at the same time.

thanks
thomas

View 2 Replies View Related

Can't Execute Master.dbo.xp_cmdshell Throught A Variable.

Dec 5, 2007



Hi all.

I've previous posted a message with error building the expression of the dtexec.exe string. But now, the problem is other, and I've decided to create other threat.

Here is what I'm doing:
I've a SP that receives every parameter so I can build my dtexec.exe statement to be executed by a variable with something like this exec @rc = master.dbo.xp_cmdshell @CMD


Till now I have no problem executing it, not from a variable, but just with the statement itself, like this:
exec master.dbo.xp_cmdshell 'C:PROGRA~2MICROS~290DTSBINNDTEXEC.EXE /SQ PACKAGE /SET "Package.Variables[PERIOD].Value";20070101'.

But now, I have this SP that builds the string into a variable and then I just want to execute it throught the variable like this: exec @rc = master.dbo.xp_cmdshell @CMD.

When I make a SELECT @CMD just before the exec @rc = master.dbo.xp_cmdshell @CMD, so it prints the builded string, copy it and pasted to exec master.dbo.xp_cmdshell 'copied string' it works fine. But the step of exec @rc = master.dbo.xp_cmdshell @CMD returns the error:
The filename, directory name, or volume label syntax is incorrect.

Why is this, if I copy the generated string and execute it by the other way it works fine, but executing the generated string throught the variable it gives me that error?

View 4 Replies View Related

Execute Xp_cmdshell From Store Procedure (called From Aspx)

May 21, 2004

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."

The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155

The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------------------------------------------------
EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'

Thanks for your help
ehx5

View 1 Replies View Related

Best Method For Using FTP Command Xp_cmdshell Or Execute Process Task

Dec 8, 2005

Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.


---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,

View 1 Replies View Related

Other Way To Execute The Xp_cmdshell Command On Remote Databases From Local?

Nov 14, 2007

Hi all,


Criteria:

We connect to the remote database servers through the network from loca by using Query Analyzer.
Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data.
But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer
We do not know what happened but i think due to network updates this command is not able to execute...


For ex:
Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute


Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?


Note : we are able to execute this command on remote Query Analyzer but not from local QA

Any suggestions would be very very helpful to me?


Thanks in advance,



View 6 Replies View Related

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

Aug 7, 2006

Hi,I am having
some trouble copying data over my workgroup network from my Windows
2003 Server Machine (machineA with SQL SERVER 2005) to one of my
network Machine's drive(MachineB).Here is the T-SQL code that I am trying to execute:EXEC xp_cmdshell 'copy D:Datafile.txt  \MachineBDocuments'Whenever
I tried to execute the above piece of code, I get the error message
"Access is denied", but if I try to copy the file from the Command
Prompt (cmd.exe) with the copy command, the file copies fine over the
network.I have already searched over the internet and I found
out that loads of people have the same issue, and they were suggested
something like this:"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."Well
it sounds plausible, but I don't know what are the exact steps to do
this. How do I know which user is running the MSSQL Server service? Are
they referring to the user which I use to connect to my SQL Server
Database engine throuhg the SQL Server Management Studio?Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.Here
are some details of the user that I use to login. I generally login
into my Windows 2003 Server machine with user called 'User1' and I use
the same 'User1' to connect to SQL Server through the Management Studio
Screen.Should I create a user called 'User1' on my MachineB(Destination Machine)?I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.Thank you very much once again.

View 11 Replies View Related

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

Aug 7, 2006

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:Datafile.txt \MachineBDocuments'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.

View 18 Replies View Related

Using XP_CmdShell To Execute BCP Utility With QUERYOUT. How To Control The OWNER Of The File Created?

Jan 29, 2008

I need the file created by my BCP QUERYOUT command I'm executing from XP_CmdShell in Transact-SQL to have a specific OWNER. I've tried using the sp_xp_cmdshell_proxy_account to setup the correct owner. The owner is always SERVERNAMEAdministrators.

thanks,

jim




View 1 Replies View Related

Xp_cmdshell Does Not Execute For Non-sysadmin Account Even With Proxy Account

Mar 2, 2004

Hi all, i hope you can help me.

Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.

I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.

Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"

So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.

The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.

Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '

hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.

I have search the net for a few days now and yet i can find no solution.

Can anyone help?

View 2 Replies View Related

Database Restore Rights - Sql Server 2005

Oct 30, 2007

Is there a way to grant rights to a user to be able to restore a specific database on a server, without being able to muck around with other databases on the server?

View 1 Replies View Related

Install Visual Studio 2005 Rights Issue

Apr 28, 2006

Hello-

I am trying to install VS2005 Standard Edition on W2k Sp4. This install fails on the first part when MDAC 2.8 Sp1 tries to install. I am logged on as ADMINISTRATOR with admin rights. I tried to install MDAC 2.8 sp1 alone with the same result. Can you help?

Vincent DeLuca

vdeluca7@centurytel.net



View 1 Replies View Related

Legacy DTS Package On SQL 2005 - Give User Rights To Save DTS Packages...

Oct 4, 2007

We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.

Thanks

View 6 Replies View Related

Performing FTP Using Xp_cmdshell In Ms Sql 2005

Feb 27, 2008



Hi,
I want to copy xls file from remote server to my server.
i want to use xp_cmdshell to perform this operation.
can anybody help me out how to pass parameter to xp_cmdshell.

thanks in advance,

Chetan S. Raut.

View 3 Replies View Related

Installing SQL 2005 In An Existing W2k3 Cluster - Is Domain Admin Rights Required?

Apr 17, 2008

I have DBA that is convinced that they need domain admin rights to install SQL 2005 into an existing cluster. The domain groups and service accounts for SQL have been created already. Is having domain admin rights required during the install of SQL 2005 in a cluster?

View 1 Replies View Related

Yet Another Xp_cmdshell Problem SQL Server 2005

Nov 16, 2006

Hi,

I have to named instances on one server. I'd like to use xp_cmdshell for some interfaces.

I've "activated" the xp_cmdshell feature. I set a proxy account und and gave the sql user the right to exec xp_cmdshell.

Unfortunately the command

EXEC sp_xp_cmdshell_proxy_account 'DOMAINproxyaccount','APassword'

throws following error:

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.

... and yes, the username and password should be valid ;-)

When I exec (makes no sense, I now...)

EXEC xp_cmdshell 'echo 123'

The following error occurs:

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.

Has anyone of you an idea?

Thank you very much, Frederik

View 9 Replies View Related

Setting Admin Rights To Report Manager, SQL Server Express 2005 On Vista Business

Mar 7, 2007

Hi all,

I'm a newbie to Reporting Services so please forgive my ignorance...

I've recently installed SQL Server Express 2005 with Reporting Services on my Vista Business Notebook (with no network connections) and have installed VS C# Express as well as the Business Intelligence Studio to create reports.

My C# application can connect to SQL Server fine, however, the reports I created with Business Studio refuses to deploy to the server due to the following reason:

"The permissions granted to user 'localhostJenny' are insufficient for
performing this operation".

I find this odd as I've been able to deploy reports on my other Windows XP machine (with SQL Server Express) no probs at all. Additionally, I cannot see the 'Site settings' link within Report Manager (Web) that I'd normally get with an admin account (i.e. on my Win. XP machine). Various sources indicate that my account has insufficient access rights to view this.

Having browsed through seemingly endless blogs and forums, I suspect that my local account has not been configured to access the Report Manager web client to publish, manage and administer the site.

I have tried the surface area tool for SQL Server, but I suspect that this is relevant only to SQL Server DB access and management. Adding an administrator here does nothing.

The question is, how can I grant access to myself (local account) to allow me to publish reports via business studio, and at the same time - administer Report Manager Site Settings - on my vista notebook??

I would be extremely grateful for any help on this matter - I've tried ceaseless reinstallations, but to no avail!


Many thanks in advance,

Jenny

View 5 Replies View Related

2000 &> 2005 And Xp_cmdshell Stopped Working!

Jun 18, 2008

Hi,

I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.

I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.

This is the line causing the problem (if I take it out, the rest of the SP works no problems):

--write the text file
EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'


Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.

SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?

Any help would be greatly appreciated!

Thanks

View 6 Replies View Related

Administrative Commands Via XP_CmdShell In SQL Server 2005

Dec 13, 2007

SQL Server 2005: Microsoft SQL Server Enterprise Edition (64-bit)
Microsoft Windows NT 5.2 (3790)

SQL Version 9.00.3175.00


I have 2 identical servers... one for DEV and one for TEST. On the DEV server, I can use xp_cmsdhell to execute "administrative commands" such as NET SHARE and AT. On the identically configured TEST server, I can not. Those command return the "System error 5 has occurred. Access is denied" error. I CAN run other commands via xp_cmdshell (DIR, NET USE, etc.). This is true regardless of the account I connect as.

The SQL Server service accounts in both instances are domain accounts belonging to the local admin group.

I "know" that I ran across this when setting up the DEV server and am "fairly sure" I found a reference to a known problem with 2005's xp_cmdshell and administrative commands. But for the life of me I can not now find any such reference, nor can I find any differences between the DEV and TEST servers in the area of users, groups, policies, or anything else security-related. (99.9% of the internet searches I do end up being about either enabling xp_cmdshell -- done -- or setting up an xp_cmdshell proxy account -- not needed).

Any input is greatly appreciated.

Thanks!
-dave

View 7 Replies View Related

Xp_cmdshell Statement Worked In SQL2000 Not In 2005

Dec 7, 2007

Code Block
Hi All,

I've got a stored procedure called from a trigger on another database. There are two xp_cmdshell statements in the procedure. One writes a couple variables, passed from the trigger, into a text file. The second fires off an executable that uses the text file via a batch file.

Here's the code that worked on the old 32bit SQL2000 box





Code Block
begin
declare @cmd varchar(150)
select @cmd = 'echo ' + @sVariable1 + ',' + @sVariable2 +' > c:aFolderTextFile.txt'
exec master..xp_cmdshell @cmd

declare @cmd2 varchar(150)
select @cmd2 = 'c:aFolderRun.bat'
exec master..xp_cmdshell @cmd2
end






The new box is 64bit SQL2005. When invoked the stored procedure executes without an error. The textfile is written sucessfully. But, the executable called from the second xp_shell statement does not produce the expected result. Because there is no error I'm having trouble determining where the failure lies.


To troubleshoot the problem I've tried:



Running the batch file from the command prompt in the system32 directory... Success


Using sp_xp_cmdshell_proxy_account to be sure of the credentials of the running xp_cmdshell... Failure

My questions:

Does 64 Bit SQL2005 have a different set of parameters when invoking an executable from xp_cmdshell? ie Do I need to rewrite the batch file?
Is there another way call an executable, SQLCMD maybe?

Any help is appreciated...




Code Block
The batch file if you're curious:

"c:Program Files (x86)someVendersomeAppsomeEXE.exe" "C:aFolderTextFile.txt"








View 3 Replies View Related

Xp_cmdshell Problem SQL Server 2005; Or Perhaps I Am Attempting The Impossible...

Mar 7, 2008

I am using the xp_cmdshell to run a batch file that is trying to open the Adobe Acrobat Reader.

When I run the batch file in the command prompt, it opens perfectly.

When I run it in SQL with xp_cmdshell, it returns the contents fine, and I notice it tries to open Acrobat, but it is under the username "system". And I cannot get it to load in a visible area, and for some reason it doesnt seem to load properly under system.

Any ideas? Any Suggestions?

Please advise.

Marco

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved