Qsuiap5f

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
7
8INSERT INTO @Databases(dbname, flag)
9SELECT name, 0
10FROM sys.databases
11WHERE name NOT IN ('master','tempdb','model','msdb')
12
13WHILE EXISTS(SELECT TOP 1 dbname FROM @Databases WHERE flag = 0)
14BEGIN
15
16      SELECT
17      @name = dbname
18      FROM @Databases
19      WHERE flag = 0
20
21	  --Creates the role db_executor if it doesnt exist then it sets the user group to that role
22
23      SET @dsql = 'USE [@name]
24      
25     
26      
27      BEGIN
28      
29        
30      IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = ''db_executor'')
31      CREATE ROLE db_executor
32	  GRANT EXECUTE TO db_executor
33	
34	  IF NOT EXISTS (SELECT NAME FROM sysusers WHERE NAME = ''DOMAIN\Dev SQL access for Operations services'')
35      CREATE USER [SYSTEM\Dev SQL access for Operations services] FOR LOGIN [DOMAIN\Dev SQL access for Operations services]
36	  EXEC sp_addrolemember db_executor,"DOMAIN\Dev SQL access for Operations services"
37
38      END'
39      
40      SET @dsql = REPLACE(REPLACE(@dsql, '@name', @name), '@singlequote', @singlequote);
41      
42      EXEC sp_executesql @dsql;
43      
44      UPDATE @Databases
45      SET flag = 1
46      WHERE dbname = @name
47      AND flag = 0
48
49END