suCUjuPK

1DECLARE @Databases TABLE(dbname VARCHAR(50), flag BIT)  -- database table
2DECLARE @name VARCHAR(50) -- database name
3DECLARE @dsql NVARCHAR(MAX);
4DECLARE @dsql2 NVARCHAR (MAX);
5DECLARE @singlequote CHAR(1) = '''';
6
7INSERT INTO @Databases(dbname, flag)
8SELECT name, 0
9FROM sys.databases
10WHERE name NOT IN ('master','tempdb','model','msdb')
11
12WHILE EXISTS(SELECT TOP 1 dbname FROM @Databases WHERE flag = 0)
13BEGIN
14
15      SELECT
16      @name = dbname
17      FROM @Databases
18      WHERE flag = 0
19
20      SET @dsql = 'USE [@name]
21      
22      IF NOT EXISTS (SELECT NAME FROM sysusers WHERE NAME = ''DOMAIN\Dev SQL access for Operations services'')
23      
24      BEGIN
25      
26      CREATE USER [SYSTEM\Dev SQL access for Operations services] FOR LOGIN [DOMAIN\Dev SQL access for Operations services]
27     
28      CREATE ROLE db_executor
29
30      GRANT EXECUTE TO db_executor
31
32      EXEC sp_addrolemember 'db_executor','DOMAIN\Dev SQL access for Operations services'
33
34    
35      END'
36      
37      SET @dsql = REPLACE(REPLACE(@dsql, '@name', @name), '@singlequote', @singlequote);
38      
39      EXEC sp_executesql @dsql;
40      
41      UPDATE @Databases
42      SET flag = 1
43      WHERE dbname = @name
44      AND flag = 0
45
46END