SQL Server Performance

SQL 2005 check for existance of proxy

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Jul 28, 2006.

  1. SQL_Guess New Member

    I'm try to find a good way to create the LOGIN, USERS, GRANTS, CREDENTIALS and PROXY for an SSIS Package to be executed from an agent job using cmdexec.<br /><br />The following code attempts to do this. I have an issue around the<br />'SP_REVOKE_PROXY_FROM_SUBSYSTEM ' and<br />'SP_GRANT_PROXY_TO_SUBSYSTEM'. If haven't found a way of determining if the PROXY has rights on the subsysttem. Unfortunately, if it doesn't, then the revoke throws an error, and while the GRANT then runs and finishes the scripts work successfully, it isn't very clean.<br /><br />Also, I'd love to hear of better ways of doing any of the below - there doesn't seem to be INFORMATION_SCHEMA views for the things I'm checking.<br /><br />-----------------------------------code start<br /><pre id="code"><font face="courier" size="2" id="code"><br />--###################################################### describe script ############################################################################<br />PRINT '&gt;&gt;&gt; This script creates the LOGIN, USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES &lt;&lt;&lt;'<br />PRINT '&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] &lt;&lt;&lt;'<br />PRINT ''<br />PRINT '&gt;&gt;&gt; Create Objects &lt;&lt;&lt;'<br />PRINT ''<br /><br />--################################################## Check and Drop Existing ########################################################################<br />DECLARE<br /> @proxy_name SYSNAME,<br /> @subsystem_name SYSNAME,<br /> @UserName SYSNAME,<br /> @credential_name SYSNAME,<br /> @RowCount INT<br />SET @credential_name = 'TestProxy2'<br />SET @proxy_name = 'TestSSISUser2'<br />SET @subsystem_name = 'CmdExec'<br />SET @UserName = 'MyLapTopTestSSISUser2'<br /><br />--################################################## CREATE Login ###################################################################################<br />SET NOCOUNT ON<br />USE [master]<br />IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = @UserName)<br /> CREATE LOGIN [MyLapTopTestSSISUser2] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDB]<br />--################################################## CREATE User and Grant Rights on DBS ############################################################<br />USE [MyDB]<br />IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'MyLapTopTestSSISUser2')<br /> CREATE USER [MyLapTopTestSSISUser2] FROM LOGIN [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [DB_DataReader], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [DB_DataWriter], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [MyLapTopTestSSISUser2]<br />GRANT EXECUTE ON [sp_dts_addlogentry] to [MyLapTopTestSSISUser2]<br />USE [msdb]<br />IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'MyLapTopTestSSISUser2')<br /> CREATE USER [MyLapTopTestSSISUser2] FROM LOGIN [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [db_dtsadmin], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [db_dtsltduser], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [db_dtsoperator], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [MyLapTopTestSSISUser2]<br />EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [MyLapTopTestSSISUser2]<br /><br />--################################################## CREATE Credential ##############################################################################<br />USE [master]<br />IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = @credential_name)<br /> CREATE CREDENTIAL [TestProxy2] WITH IDENTITY = 'MyLapTopTestSSISUser2', secret = 't3st'<br />--################################################## CREATE Proxy ###################################################################################<br />USE [msdb]<br />DECLARE @ProxyTable TABLE (subsystem_id int ,subsystem_name sysname,proxy_id int,proxy_name sysname)<br />INSERT INTO @ProxyTable EXEC sp_enum_proxy_for_subsystem --@proxy_name=@proxy_name, @subsystem_name=@subsystem_name<br />select proxy_name from @ProxyTable WHERE proxy_name = @proxy_name AND subsystem_name = @subsystem_name<br />SELECT @RowCount = @@ROWCOUNT<br />IF @RowCount = 0 BEGIN<br /> PRINT 'Creating Proxy'<br /> EXEC SP_ADD_PROXY @proxy_name=@proxy_name, @credential_name=@credential_name<br />END<br />DECLARE @LoginProxyTable TABLE (proxy_id int,proxy_name sysname, flags int, [name] sysname, sid varbinary(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,principal_id int)<br />INSERT INTO @LoginProxyTable EXEC sp_enum_login_for_proxy --@proxy_name=@proxy_name, @name=@UserName<br />select proxy_name from @LoginProxyTable WHERE proxy_name = @proxy_name AND [name] = @UserName<br />SELECT @RowCount = @@ROWCOUNT<br />IF @RowCount = 0 BEGIN<br /> PRINT 'Granting Login to Proxy'<br /> EXEC SP_GRANT_LOGIN_TO_PROXY @login_name=@UserName, @proxy_name=@proxy_name<br />END<br />EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @proxy_name=@proxy_name, @subsystem_name=@subsystem_name<br />EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @proxy_name=@proxy_name, @subsystem_name=@subsystem_name<br /><br />--###################################################### end script ##################################################################################<br />PRINT '&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] ended at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] &lt;&lt;&lt;'<br /></font id="code"></pre id="code"><br />-----------------------------------code end<br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page