I need execute a sp that executes a xp_cmdshell
-
Hello masters i need your help to do the next thing.
one user of my database only have permission to execute a sp that it executes inside the sp xp_cmdshell , as this user has no permission to execute xp_cmdshell because it is not dbowner, it casts error, i need that user could execute the sp which it has the xp_cmdshell inside, but it can not execute the xp directly
well i hope that i have explained my problem correctly, thank you for your helping
-
Try the links below and create a proxy account for SQL Server Agent. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Hector,
What does the stored proc do with the command shell? There might be some better ways to do things without have to use command shell...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well because i need to do a insert inside of a transaction with rollback, well i do that insert using the isql in a cmd shell using the xp_cmdshell
CREATE PROC dbo.SP_LogIsql @query varchar(8000)
as
BEGIN
declare @server varchar(30),
@LogFile varchar (255),
@PathLogvarchar(500),
@iReturnCode int,
@error_catch int,
@mensaje varchar(500)
select @server = @@servername
set @query = ' isql.exe -S' + @server + ' -Usa -Ppassword -dMybase -Q' + char(34) + @query + char(34)
exec @iReturnCode= master..xp_cmdshell @query
select @error_catch = @@error
if @iReturnCode 0 or @error_catch 0
begin
print ' Error master..xp_cmdshell '+ left(@query,100)
select @mensaje = description from master..sysmessages where error = @error_catch
insert into log_error values( @mensaje +'Error master..xp_cmdshell',getdate(),@error_catch)
end
END
-
Thanks Hector,
Gift Peddie posted a solution we use at work... Proxy Accounts work great for this.
The other thing you could do is to create a linked server for the other servers instead of using ISQL. Use the 4 part naming convention as part of your dynamic SQL.
By the way, if you end up using the Proxy Account (so you don't have to change any existing calling code), you may want to change from the call to ISQL to a call to OSQL. BOL says that ISQL is a bit deprecated ...
All DB-Library applications, such as isql, work as SQL Server 6.5–level clients when connected to SQL Server 2000. They do not support some SQL Server 2000 features. The osql utility is based on ODBC and does support all SQL Server 2000 features. Use osql to run scripts that isql cannot run. For more information about the restrictions on SQL Server 6.5–level clients, see Connecting Early Version Clients to SQL Server 2000 in SQL Server Books Online.
The SQL Query Analyzer default is to save SQL scripts as Unicode files. The isql utility does not support Unicode input files. Attempting to specify one of these files in the -i switch results in a 170 error:
...and I've personally had some code not run in ISQL that ran just fine in OSQL (although I can't remember what it couldn't run... it was years ago).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
P.S. Using the Linked Server method would keep you from exposing server logins in code, anywhere.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If the purpose is to retain partial loginfo during a transaction that is rolled back, there is a way to use a table variable as placeholder whithin the transaction. Since table variables aren't within any transaction scope, it will survive a rollback.
It works for a scenario like this: assume we have a procedure that does three steps of work within a transaction. After each step, some loginformation is to be written into a permanent logtable.
This simple demo does two successful steps, and then does a rollback before the third.
The expected end result is that the transaction is rolled back, but the logtable contains records upto the
last successful step.
use tempdb
go
-- create a worktable and a logtable
create table dbo.test1 (id int not null, someVal varchar(10) not null)
go
create table dbo.log1 ( errorTxt varchar(25) not null)
go
-- the example proc
create proc dbo.iTest1
as
set nocount on
BEGIN TRAN
declare @tmplog table
( logtext varchar(25) not null )
-- do some work
insert dbo.test1 select 1, 'step1'
-- log work done
insert @tmplog select 'step1'
-- do some more work
insert dbo.test1 select 1, 'step2'
-- log work done
insert @tmplog select 'step2'
-- do yet more work
insert dbo.test1 select 1, 'step3'
-- error happens! (comment out the below line to make the proc commit cleanly)
goto errhandler
-- log work done
insert @tmplog select 'step3'
-- when all is done, flush @tmplog to permanent logtable and commit
insert dbo.log1 select * from @tmplog
COMMIT TRAN
return
errhandler:
if ( @@trancount > 0 ) ROLLBACK
-- tran rolled back, flush cached log to permanent logtable
insert dbo.log1 select * from @tmplog
return
go
-- Now try out the demo
-- start work
exec dbo.itest1
go
-- check that no work was done, but there are logrecords
select * from test1
select * from log1
go
id someVal
----------- ----------
(0 row(s) affected)
errorTxt
-------------------------
step1
step2
(2 row(s) affected)
drop table dbo.log1, dbo.test1
drop proc dbo.iTest1
go
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply