DTS Security called from sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DTS Security called from sp

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-04-12 : 01:28:30

Trying to workout the proper security setup for a DTS package being executed by SQL login account from stored proc

Created a DTS package that does a
select * from Orders
It works with System Admin rights, but I don't want to do this so

I granted xp_cmdshell to dts_user
Northwind with db_dlladmin and db_datareader

I can't see what am I missing please help

exec northwind.dbo.usp_run_sql

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_run_sql]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_run_sql]
GO

CREATE procedure dbo.usp_run_sql
as
begin
declare @ret int , @str varchar , @sql_str varchar(255), @msg varchar(255)

set @str = '1'

set @msg = 'DTSRun /S "(local)" /U "dts_user" /P "elton1964" /N "run_sql"'
exec @ret=master.dbo.xp_cmdshell @sql_str , NO_OUTPUT
if (@ret=0)
begin
return @ret
end
else
begin
raiserror ('Package failed to load data. Please contact the DBA.',16,1)
return -1
end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[usp_run_sql] TO [dts_user]
GO

You can do anything at www.zombo.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-04-12 : 02:08:39

Got it... used SP_OA procs
Hey Darren, Thank you for the article on calling DTS Packages.

http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1

declare @hr int, @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Loading the Package:
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("(Local)", "", "", 256, , , , "run_sql")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END

--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources