· 8 years ago · Jul 02, 2018, 09:32 PM
1USE [master] ;
2GO
3IF EXISTS
4(
5 SELECT * FROM sys.objects
6 WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
7 AND [type] in (N'P',N'PC')
8)
9BEGIN
10 DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
11END
12GO
13CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
14AS
15SET NOCOUNT ON
16;
17BEGIN TRY
18 IF EXISTS
19 (
20 SELECT * FROM tempdb.dbo.sysobjects
21 WHERE id = object_id(N'[tempdb].dbo.[#permission]')
22 )
23 DROP TABLE #permission
24 ;
25 IF EXISTS
26 (
27 SELECT * FROM tempdb.dbo.sysobjects
28 WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
29 )
30 DROP TABLE #userroles_kk
31 ;
32 IF EXISTS
33 (
34 SELECT * FROM tempdb.dbo.sysobjects
35 WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
36 )
37 DROP TABLE #rolemember_kk
38 ;
39 IF EXISTS
40 (
41 SELECT * FROM tempdb.dbo.sysobjects
42 WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
43 )
44 DROP TABLE ##db_name
45 ;
46 DECLARE
47 @db_name VARCHAR(255)
48 ,@sql_text VARCHAR(MAX)
49 ;
50 SET @sql_text =
51 'CREATE TABLE ##db_name
52 (
53 LoginUserName VARCHAR(MAX)
54 ,'
55 ;
56 DECLARE cursDBs CURSOR FOR
57 SELECT [name]
58 FROM sys.databases
59 ORDER BY [name]
60 ;
61 OPEN cursDBs
62 ;
63 FETCH NEXT FROM cursDBs INTO @db_name
64 WHILE @@FETCH_STATUS = 0
65 BEGIN
66 SET @sql_text =
67 @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
68 ,'
69 FETCH NEXT FROM cursDBs INTO @db_name
70 END
71 CLOSE cursDBs
72 ;
73 SET @sql_text =
74 @sql_text + 'IsSysAdminLogin CHAR(1)
75 ,IsEmptyRow CHAR(1)
76 )'
77
78 --PRINT @sql_text
79 EXEC (@sql_text)
80 ;
81 DEALLOCATE cursDBs
82 ;
83 DECLARE
84 @RoleName VARCHAR(255)
85 ,@UserName VARCHAR(255)
86 ;
87 CREATE TABLE #permission
88 (
89 LoginUserName VARCHAR(255)
90 ,databasename VARCHAR(255)
91 ,[role] VARCHAR(255)
92 )
93 ;
94 DECLARE cursSysSrvPrinName CURSOR FOR
95 SELECT [name]
96 FROM sys.server_principals
97 WHERE
98 [type] IN ( 'S', 'U', 'G' )
99 AND principal_id > 4
100 AND [name] NOT LIKE '##%'
101 ORDER BY [name]
102 ;
103 OPEN cursSysSrvPrinName
104 ;
105 FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
106 WHILE @@FETCH_STATUS = 0
107 BEGIN
108 CREATE TABLE #userroles_kk
109 (
110 databasename VARCHAR(255)
111 ,[role] VARCHAR(255)
112 )
113 ;
114 CREATE TABLE #rolemember_kk
115 (
116 dbrole VARCHAR(255)
117 ,membername VARCHAR(255)
118 ,membersid VARBINARY(2048)
119 )
120 ;
121 DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
122 SELECT [name]
123 FROM sys.databases
124 ORDER BY [name]
125 ;
126 OPEN cursDatabases
127 ;
128 DECLARE
129 @DBN VARCHAR(255)
130 ,@sqlText NVARCHAR(4000)
131 ;
132 FETCH NEXT FROM cursDatabases INTO @DBN
133 WHILE @@FETCH_STATUS = 0
134 BEGIN
135 SET @sqlText =
136 N'USE ' + QUOTENAME(@DBN) + ';
137 TRUNCATE TABLE #RoleMember_kk
138 INSERT INTO #RoleMember_kk
139 EXEC sp_helprolemember
140 INSERT INTO #UserRoles_kk
141 (DatabaseName,[Role])
142 SELECT db_name(),dbRole
143 FROM #RoleMember_kk
144 WHERE MemberName = ''' + @UserName + '''
145 '
146
147 --PRINT @sqlText ;
148 EXEC sp_executesql @sqlText ;
149 FETCH NEXT FROM cursDatabases INTO @DBN
150 END
151 CLOSE cursDatabases
152 ;
153 DEALLOCATE cursDatabases
154 ;
155 INSERT INTO #permission
156 SELECT
157 @UserName 'user'
158 ,b.name
159 ,u.[role]
160 FROM
161 sys.sysdatabases b
162 LEFT JOIN
163 #userroles_kk u
164 ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
165 ORDER BY 1
166 ;
167 DROP TABLE #userroles_kk
168 ;
169 DROP TABLE #rolemember_kk
170 ;
171 FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
172 END
173 CLOSE cursSysSrvPrinName
174 ;
175 DEALLOCATE cursSysSrvPrinName
176 ;
177 TRUNCATE TABLE ##db_name
178 ;
179 DECLARE
180 @d1 VARCHAR(MAX)
181 ,@d2 VARCHAR(MAX)
182 ,@d3 VARCHAR(MAX)
183 ,@ss VARCHAR(MAX)
184 ;
185 DECLARE cursPermisTable CURSOR FOR
186 SELECT * FROM #permission
187 ORDER BY 2 DESC
188 ;
189 OPEN cursPermisTable
190 ;
191 FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
192 WHILE @@FETCH_STATUS = 0
193 BEGIN
194 IF NOT EXISTS
195 (
196 SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
197 )
198 BEGIN
199 SET @ss =
200 'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
201 EXEC (@ss)
202 ;
203 SET @ss =
204 'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
205 EXEC (@ss)
206 ;
207 END
208 ELSE
209 BEGIN
210 DECLARE
211 @var NVARCHAR(MAX)
212 ,@ParmDefinition NVARCHAR(MAX)
213 ,@var1 NVARCHAR(MAX)
214 ;
215 SET @var =
216 N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
217 ;
218 SET @ParmDefinition =
219 N'@var1 NVARCHAR(600) OUTPUT '
220 ;
221 EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
222 ;
223 SET @var1 =
224 ISNULL(@var1, ' ')
225 ;
226 SET @var =
227 ' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
228 ;
229 EXEC (@var)
230 ;
231 END
232 FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
233 END
234 CLOSE cursPermisTable
235 ;
236 DEALLOCATE cursPermisTable
237 ;
238 UPDATE ##db_name SET
239 IsSysAdminLogin = 'Y'
240 FROM
241 ##db_name TT
242 INNER JOIN
243 dbo.syslogins SL
244 ON TT.LoginUserName = SL.[name]
245 WHERE
246 SL.sysadmin = 1
247 ;
248 DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
249 SELECT [name]
250 FROM tempdb.sys.columns
251 WHERE
252 OBJECT_ID = OBJECT_ID('tempdb..##db_name')
253 AND [name] NOT IN ('LoginUserName','IsEmptyRow')
254 ORDER BY [name]
255 ;
256 OPEN cursDNamesAsColumns
257 ;
258 DECLARE
259 @ColN VARCHAR(255)
260 ,@tSQLText NVARCHAR(4000)
261 ;
262 FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
263 WHILE @@FETCH_STATUS = 0
264 BEGIN
265 SET @tSQLText =
266N'UPDATE ##db_name SET
267IsEmptyRow = ''N''
268WHERE IsEmptyRow IS NULL
269AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
270;
271'
272
273 --PRINT @tSQLText ;
274 EXEC sp_executesql @tSQLText ;
275 FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
276 END
277 CLOSE cursDNamesAsColumns
278 ;
279 DEALLOCATE cursDNamesAsColumns
280 ;
281 UPDATE ##db_name SET
282 IsEmptyRow = 'Y'
283 WHERE IsEmptyRow IS NULL
284 ;
285 UPDATE ##db_name SET
286 IsSysAdminLogin = 'N'
287 FROM
288 ##db_name TT
289 INNER JOIN
290 dbo.syslogins SL
291 ON TT.LoginUserName = SL.[name]
292 WHERE
293 SL.sysadmin = 0
294 ;
295 SELECT * FROM ##db_name
296 ;
297 DROP TABLE ##db_name
298 ;
299 DROP TABLE #permission
300 ;
301END TRY
302BEGIN CATCH
303 DECLARE
304 @cursDBs_Status INT
305 ,@cursSysSrvPrinName_Status INT
306 ,@cursDatabases_Status INT
307 ,@cursPermisTable_Status INT
308 ,@cursDNamesAsColumns_Status INT
309 ;
310 SELECT
311 @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
312 ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
313 ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
314 ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
315 ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
316 ;
317 IF @cursDBs_Status > -2
318 BEGIN
319 CLOSE cursDBs ;
320 DEALLOCATE cursDBs ;
321 END
322 IF @cursSysSrvPrinName_Status > -2
323 BEGIN
324 CLOSE cursSysSrvPrinName ;
325 DEALLOCATE cursSysSrvPrinName ;
326 END
327 IF @cursDatabases_Status > -2
328 BEGIN
329 CLOSE cursDatabases ;
330 DEALLOCATE cursDatabases ;
331 END
332 IF @cursPermisTable_Status > -2
333 BEGIN
334 CLOSE cursPermisTable ;
335 DEALLOCATE cursPermisTable ;
336 END
337 IF @cursDNamesAsColumns_Status > -2
338 BEGIN
339 CLOSE cursDNamesAsColumns ;
340 DEALLOCATE cursDNamesAsColumns ;
341 END
342 SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
343END CATCH
344GO
345
346EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;