SCRIPT FOR CREATING A LOGIN ON MULTIPLE SERVERS

DECLARE
@SERVERNAME VARCHAR(100),
@SQL VARCHAR(2000)

DECLARE CURSORSERVER CURSOR FOR

SELECT
SERVERNAME
FROM
dbacentral.dbo.servers
WHERE RETIRED ='9999-12-31 00:00:00.000' AND ENABLED = 1
AND SERVERNAME NOT IN ('BHSF-BTR-W310','BHSF-BTR-W333')
AND SQLVersion NOT LIKE '%Microsoft SQL Server  2000%'


OPEN CURSORSERVER
FETCH NEXT FROM CURSORSERVER INTO @SERVERNAME

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SERVERNAME
SET @SQL ='CREATE LOGIN [dbu_DbaCentral] WITH PASSWORD=N''''dbudc'''', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [dbu_DbaCentral] FOR LOGIN [dbu_DbaCentral]
GRANT SELECT ON msdb.dbo.sysjobs TO [dbu_DbaCentral]
GRANT execute ON msdb.dbo.sp_get_composite_job_info TO [dbu_DbaCentral]'

EXEC('exec [' + @ServerName + '].msdb.dbo.sp_executesql ' + 'N''' + @SQL + '''')

FETCH NEXT FROM CURSORSERVER INTO @SERVERNAME
END

CLOSE CURSORSERVER
DEALLOCATE CURSORSERVER