· 8 years ago · Aug 13, 2018, 02:04 PM
1/****************************************************************
2This Script Generates A script to Create all Logins, Server Roles
3, DB Users and DB roles on a SQL Server
4
5Greg Ryan
6
710/31/2013
8****************************************************************/
9SET NOCOUNT ON
10
11DECLARE
12 @sql nvarchar(max)
13, @Line int = 1
14, @max int = 0
15, @@CurDB nvarchar(100) = ''
16
17CREATE TABLE #SQL
18 (
19 Idx int IDENTITY
20 ,xSQL nvarchar(max)
21 )
22
23INSERT INTO #SQL
24 ( xSQL
25 )
26 SELECT
27 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
28 + QUOTENAME(name) + ''')
29' + ' CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
30 + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
31 + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
32 + QUOTENAME(COALESCE(default_database_name , 'master'))
33 + ', DEFAULT_LANGUAGE='
34 + QUOTENAME(COALESCE(default_language_name , 'us_english'))
35 + ', CHECK_EXPIRATION=' + CASE is_expiration_checked
36 WHEN 1 THEN 'ON'
37 ELSE 'OFF'
38 END + ', CHECK_POLICY='
39 + CASE is_policy_checked
40 WHEN 1 THEN 'ON'
41 ELSE 'OFF'
42 END + '
43Go
44
45'
46 FROM
47 sys.sql_logins
48 WHERE
49 name <> 'sa'
50
51INSERT INTO #SQL
52 ( xSQL
53 )
54 SELECT
55 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
56 + QUOTENAME(name) + ''')
57' + ' CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
58 + 'DEFAULT_DATABASE='
59 + QUOTENAME(COALESCE(default_database_name , 'master'))
60 + ', DEFAULT_LANGUAGE='
61 + QUOTENAME(COALESCE(default_language_name , 'us_english'))
62 + ';
63Go
64
65'
66 FROM
67 sys.server_principals
68 WHERE
69 type IN ( 'U' , 'G' )
70 AND name NOT IN ( 'BUILTIN\Administrators' ,
71 'NT AUTHORITY\SYSTEM' );
72
73PRINT '/*****************************************************************************************/'
74PRINT '/*************************************** Create Logins ***********************************/'
75PRINT '/*****************************************************************************************/'
76SELECT
77 @Max = MAX(idx)
78 FROM
79 #SQL
80WHILE @Line <= @max
81 BEGIN
82
83
84
85 SELECT
86 @sql = xSql
87 FROM
88 #SQL AS s
89 WHERE
90 idx = @Line
91 PRINT @sql
92
93 SET @line = @line + 1
94
95 END
96DROP TABLE #SQL
97
98CREATE TABLE #SQL2
99 (
100 Idx int IDENTITY
101 ,xSQL nvarchar(max)
102 )
103
104INSERT INTO #SQL2
105 ( xSQL
106 )
107 SELECT
108 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
109 + QUOTENAME(R.name) + ';
110GO
111
112'
113 FROM
114 sys.server_principals L
115 JOIN sys.server_role_members RM
116 ON L.principal_id = RM.member_principal_id
117 JOIN sys.server_principals R
118 ON RM.role_principal_id = R.principal_id
119 WHERE
120 L.type IN ( 'U' , 'G' , 'S' )
121 AND L.name NOT IN ( 'BUILTIN\Administrators' ,
122 'NT AUTHORITY\SYSTEM' , 'sa' );
123
124
125PRINT '/*****************************************************************************************/'
126PRINT '/******************************Add Server Role Members *******************************/'
127PRINT '/*****************************************************************************************/'
128SELECT
129 @Max = MAX(idx)
130 FROM
131 #SQL2
132SET @line = 1
133WHILE @Line <= @max
134 BEGIN
135
136
137
138 SELECT
139 @sql = xSql
140 FROM
141 #SQL2 AS s
142 WHERE
143 idx = @Line
144 PRINT @sql
145
146 SET @line = @line + 1
147
148 END
149DROP TABLE #SQL2
150
151PRINT '/*****************************************************************************************/'
152PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
153PRINT '/*****************************************************************************************/'
154
155
156--Drop Table #Db
157CREATE TABLE #Db
158 (
159 idx int IDENTITY
160 ,DBName nvarchar(100)
161 );
162
163
164
165INSERT INTO #Db
166 SELECT
167 name
168 FROM
169 master.dbo.sysdatabases
170 WHERE
171 name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
172 ORDER BY
173 name;
174
175
176SELECT
177 @Max = MAX(idx)
178 FROM
179 #Db
180SET @line = 1
181--Select * from #Db
182
183
184--Exec sp_executesql @SQL
185
186WHILE @line <= @Max
187 BEGIN
188 SELECT
189 @@CurDB = DBName
190 FROM
191 #Db
192 WHERE
193 idx = @line
194
195 SET @SQL = 'Use ' + @@CurDB + '
196
197Declare @@Script NVarChar(4000) = ''''
198DECLARE cur CURSOR FOR
199
200Select ''Use ' + @@CurDB + ';
201Go
202IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
203 mp.[name] + '''''')
204CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
205''GO'' + CHAR(13)+CHAR(10) +
206
207''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
208Go''
209FROM sys.database_role_members a
210INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
211INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
212
213
214OPEN cur
215
216FETCH NEXT FROM cur INTO @@Script;
217WHILE @@FETCH_STATUS = 0
218BEGIN
219PRINT @@Script
220FETCH NEXT FROM cur INTO @@Script;
221END
222
223CLOSE cur;
224DEALLOCATE cur;';
225--Print @SQL
226Exec sp_executesql @SQL;
227--Set @@Script = ''
228 SET @Line = @Line + 1
229
230 END
231
232DROP TABLE #Db