SQL Server Performance Forum – Threads Archive

SQL 2005 check for existance of proxy

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. The following code attempts to do this. I have an issue around the ’SP_REVOKE_PROXY_FROM_SUBSYSTEM ‘ and ’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. 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. ———————————–code start <pre id="code"> –###################################################### describe script ############################################################################ PRINT ‘>>> This script creates the LOGIN, USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES <<<' PRINT ‘>>> This execution on server: [‘[email protected]@SERVERNAME+’] started at: [‘+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+’] <<<' PRINT ” PRINT ‘>>> Create Objects <<<' PRINT ” –################################################## Check and Drop Existing ######################################################################## DECLARE @proxy_name SYSNAME, @subsystem_name SYSNAME, @UserName SYSNAME, @credential_name SYSNAME, @RowCount INT SET @credential_name = ‘TestProxy2′ SET @proxy_name = ‘TestSSISUser2′ SET @subsystem_name = ‘CmdExec' SET @UserName = ‘MyLapTopTestSSISUser2′ –################################################## CREATE Login ################################################################################### SET NOCOUNT ON USE [master] IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = @UserName) CREATE LOGIN [MyLapTopTestSSISUser2] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDB] –################################################## CREATE User and Grant Rights on DBS ############################################################ USE [MyDB] IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = ‘MyLapTopTestSSISUser2’) CREATE USER [MyLapTopTestSSISUser2] FROM LOGIN [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [DB_DataReader], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [DB_DataWriter], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [MyLapTopTestSSISUser2] GRANT EXECUTE ON [sp_dts_addlogentry] to [MyLapTopTestSSISUser2] USE [msdb] IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = ‘MyLapTopTestSSISUser2’) CREATE USER [MyLapTopTestSSISUser2] FROM LOGIN [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [db_dtsadmin], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [db_dtsltduser], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [db_dtsoperator], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [MyLapTopTestSSISUser2] EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [MyLapTopTestSSISUser2] –################################################## CREATE Credential ############################################################################## USE [master] IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = @credential_name) CREATE CREDENTIAL [TestProxy2] WITH IDENTITY = ‘MyLapTopTestSSISUser2’, secret = ‘t3st' –################################################## CREATE Proxy ################################################################################### USE [msdb] DECLARE @ProxyTable TABLE (subsystem_id int ,subsystem_name sysname,proxy_id int,proxy_name sysname) INSERT INTO @ProxyTable EXEC sp_enum_proxy_for_subsystem [email protected][email protected]_name, @[email protected]_name select proxy_name from @ProxyTable WHERE proxy_name = @proxy_name AND subsystem_name = @subsystem_name SELECT @RowCount = @@ROWCOUNT IF @RowCount = 0 BEGIN PRINT ‘Creating Proxy' EXEC SP_ADD_PROXY @[email protected]_name, @[email protected]_name END 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) INSERT INTO @LoginProxyTable EXEC sp_enum_login_for_proxy [email protected][email protected]_name, @[email protected] select proxy_name from @LoginProxyTable WHERE proxy_name = @proxy_name AND [name] = @UserName SELECT @RowCount = @@ROWCOUNT IF @RowCount = 0 BEGIN PRINT ‘Granting Login to Proxy' EXEC SP_GRANT_LOGIN_TO_PROXY @[email protected], @[email protected]_name END EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @[email protected]_name, @[email protected]_name EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @[email protected]_name, @[email protected]_name –###################################################### end script ################################################################################## PRINT ‘>>> This execution on server: [‘[email&#1 60;protected]@SERVERNAME+’] ended at: [‘+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+’] <<<' </pre id="code"> ———————————–code end Panic, Chaos, Disorder … my work here is done –unknown

]]>