· 8 years ago · Aug 13, 2018, 02:48 PM
1/***************************************************************************************
2This Script Generates A script to Create all Logins, Server Roles, DB USErs and DB roles on a SQL Server
3
4Greg Ryan - 10/31/2013
5Cowboy DBA - 11/12/2013
6Only looks at databases which are online,
7Also added square brackets by the "USE" statements in case a database has an illegal character
8(e.g. a number at the beginning of the name)
9
10***************************************************************************************/
11
12SET NOCOUNT ON
13
14DECLARE @sql NVARCHAR(MAX)
15 ,@Line INT = 1
16 ,@max INT = 0
17 ,@@CurDB NVARCHAR(100) = ''
18
19CREATE TABLE #SQL (
20 Idx INT IDENTITY
21 ,xSQL NVARCHAR(MAX))
22
23INSERT INTO #SQL
24 (xSQL)
25 SELECT 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + QUOTENAME(name) + ''')
26' + ' CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD=' + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID=' + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE=' + QUOTENAME(COALESCE(default_database_name,'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,'us_english')) + ', CHECK_EXPIRATION=' + CASE is_expiration_checked
27 WHEN 1 THEN 'ON'
28 ELSE 'OFF'
29 END + ', CHECK_POLICY=' + CASE is_policy_checked
30 WHEN 1 THEN 'ON' ELSE 'OFF'
31 END + '
32GO
33
34'
35 FROM sys.sql_logins
36 WHERE name <> 'sa'
37
38INSERT INTO #SQL
39 (xSQL)
40 SELECT 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + QUOTENAME(name) + ''')
41' + ' CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH ' + 'DEFAULT_DATABASE=' + QUOTENAME(COALESCE(default_database_name,'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,'us_english')) + ';
42GO
43
44'
45 FROM sys.server_principals
46 WHERE type IN ('U','G')
47 AND name NOT IN ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM');
48
49
50PRINT '/*****************************************************************************************/'
51PRINT '/*************************************** Create Logins ***********************************/'
52PRINT '/*****************************************************************************************/'
53
54SELECT @Max = MAX(idx)
55FROM #SQL
56WHILE @Line <= @max
57 BEGIN
58
59 SELECT @sql = xSql
60 FROM #SQL AS s
61 WHERE idx = @Line
62 PRINT @sql
63
64 SET @line = @line + 1
65
66 END
67DROP TABLE #SQL
68
69CREATE TABLE #SQL2 (
70 Idx INT IDENTITY
71 ,xSQL NVARCHAR(MAX))
72
73INSERT INTO #SQL2
74 (xSQL)
75 SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + QUOTENAME(R.name) + ';
76GO
77
78'
79 FROM sys.server_principals L
80 JOIN sys.server_role_members RM
81 ON L.principal_id = RM.member_principal_id
82 JOIN sys.server_principals R
83 ON RM.role_principal_id = R.principal_id
84 WHERE L.type IN ('U','G','S')
85 AND L.name NOT IN ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM','sa');
86
87
88PRINT '/*****************************************************************************************/'
89PRINT '/******************************Add Server Role Members *******************************/'
90PRINT '/*****************************************************************************************/'
91
92SELECT @Max = MAX(idx)
93FROM #SQL2
94SET @line = 1
95WHILE @Line <= @max
96 BEGIN
97
98 SELECT @sql = xSql
99 FROM #SQL2 AS s
100 WHERE idx = @Line
101 PRINT @sql
102
103 SET @line = @line + 1
104
105 END
106DROP TABLE #SQL2
107
108
109PRINT '/*****************************************************************************************/'
110PRINT '/*****************Add USEr and Roles membership to Indivdual Databases********************/'
111PRINT '/*****************************************************************************************/'
112
113--Drop Table #Db
114CREATE TABLE #Db (
115 idx INT IDENTITY
116 ,DBName NVARCHAR(100));
117
118INSERT INTO #Db
119 SELECT name
120 FROM sys.databases
121 WHERE state_desc = 'ONLINE'
122 AND name NOT IN ('master','model','msdb','tempdb')
123 ORDER BY name
124
125SELECT @Max = MAX(idx)
126FROM #Db
127SET @line = 1
128--SELECT * from #Db
129
130
131--Exec sp_executesql @SQL
132
133WHILE @line <= @Max
134 BEGIN
135 SELECT @@CurDB = DBName
136 FROM #Db
137 WHERE idx = @line
138
139 SET @SQL = 'USE [' + @@CurDB + ']
140
141DECLARE @@Script NVARCHAR(4000) = ''''
142DECLARE cur CURSOR FOR
143
144SELECT ''USE [' + @@CurDB + '];
145GO
146IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
147 mp.[name] + '''''')
148CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
149''GO'' + CHAR(13)+CHAR(10) +
150
151''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
152GO''
153FROM sys.database_role_members a
154INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
155INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
156
157
158OPEN cur
159
160FETCH NEXT FROM cur INTO @@Script;
161WHILE @@FETCH_STATUS = 0
162BEGIN
163PRINT @@Script
164FETCH NEXT FROM cur INTO @@Script;
165END
166
167CLOSE cur;
168DEALLOCATE cur;';
169--PRINT @SQL
170 EXEC sp_executesql @SQL;
171--Set @@Script = ''
172 SET @Line = @Line + 1
173
174 END
175
176DROP TABLE #Db