CREATE PROCEDURE Add_User_to_ADM_Databases( @USER VARCHAR(64) ) AS BEGIN SET NOCOUNT ON DECLARE @DatabaseName NVARCHAR(100) DECLARE @SQL NVARCHAR(MAX) PRINT 'The following user has been selected to have read-only access on all user databases except system databases and log shipped databases: ' +@USER DECLARE Grant_Permission CURSOR LOCAL FOR SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','distribution','DataConstructionServer_Backup','dswMatchReview_Backup','dswTempDB') AND [state_desc]='ONLINE' AND [is_read_only] <> 1 ORDER BY name OPEN Grant_Permission FETCH NEXT FROM Grant_Permission INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @USER + ' FOR LOGIN ' + @USER + '; EXEC sp_addrolemember N''db_datareader'', ' + @USER + ''; PRINT @SQL EXEC sp_executesql @SQL Print ''-- This is to give a line space between two databases execute prints. FETCH NEXT FROM Grant_Permission INTO @DatabaseName END CLOSE Grant_Permission DEALLOCATE Grant_Permission END