Posted By

sbonnell on 05/19/11


Tagged

rolesloginpermissions


Versions (?)

Audit user login and database roles


 / Published in: SQL
 

  1. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type IN (N'U'))
  2. DROP TABLE ##Users
  3.  
  4. GO
  5. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type IN (N'U'))
  6. DROP TABLE ##DBUsers
  7.  
  8. GO
  9.  
  10. -- ***************************************************************************
  11. -- Always run this from master
  12. USE master
  13. -- ***************************************************************************
  14.  
  15. -- ***************************************************************************
  16. -- Declare local variables
  17. DECLARE @DBName VARCHAR(32)
  18. DECLARE @SQLCmd VARCHAR(1024)
  19. -- ***************************************************************************
  20.  
  21. -- ***************************************************************************
  22. -- Get the SQL Server logins
  23. SELECT sid,
  24. loginname AS [Login Name],
  25. dbname AS [DEFAULT DATABASE],
  26. CASE isntname
  27. WHEN 1 THEN 'AD Login'
  28. ELSE 'SQL Login'
  29. END AS [Login Type],
  30. CASE
  31. WHEN isntgroup = 1 THEN 'AD Group'
  32. WHEN isntuser = 1 THEN 'AD User'
  33. ELSE ''
  34. END AS [AD Login Type],
  35. CASE sysadmin
  36. WHEN 1 THEN 'Yes'
  37. ELSE 'No'
  38. END AS [sysadmin],
  39. CASE [securityadmin]
  40. WHEN 1 THEN 'Yes'
  41. ELSE 'No'
  42. END AS [securityadmin],
  43. CASE [serveradmin]
  44. WHEN 1 THEN 'Yes'
  45. ELSE 'No'
  46. END AS [serveradmin],
  47. CASE [setupadmin]
  48. WHEN 1 THEN 'Yes'
  49. ELSE 'No'
  50. END AS [setupadmin],
  51. CASE [processadmin]
  52. WHEN 1 THEN 'Yes'
  53. ELSE 'No'
  54. END AS [processadmin],
  55. CASE [diskadmin]
  56. WHEN 1 THEN 'Yes'
  57. ELSE 'No'
  58. END AS [diskadmin],
  59. CASE [dbcreator]
  60. WHEN 1 THEN 'Yes'
  61. ELSE 'No'
  62. END AS [dbcreator],
  63. CASE [bulkadmin]
  64. WHEN 1 THEN 'Yes'
  65. ELSE 'No'
  66. END AS [bulkadmin]
  67. INTO ##Users
  68. FROM dbo.syslogins
  69.  
  70. SELECT [Login Name],
  71. [DEFAULT DATABASE],
  72. [Login Type],
  73. [AD Login Type],
  74. [sysadmin],
  75. [securityadmin],
  76. [serveradmin],
  77. [setupadmin],
  78. [processadmin],
  79. [diskadmin],
  80. [dbcreator],
  81. [bulkadmin]
  82. FROM ##Users
  83. ORDER BY [Login Type], [AD Login Type], [Login Name]
  84. -- ***************************************************************************
  85.  
  86. -- ***************************************************************************
  87. -- Create the output table for the Database User ID's
  88. CREATE TABLE ##DBUsers (
  89. [DATABASE] VARCHAR(64),
  90. [DATABASE User ID] VARCHAR(64),
  91. [Server Login] VARCHAR(64),
  92. [DATABASE Role] VARCHAR(64))
  93. -- ***************************************************************************
  94.  
  95. -- ***************************************************************************
  96. -- Declare a cursor to loop through all the databases on the server
  97. DECLARE csrDB CURSOR FOR
  98. SELECT name
  99. FROM sysdatabases
  100. WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
  101. -- ***************************************************************************
  102.  
  103. -- ***************************************************************************
  104. -- Open the cursor and get the first database name
  105. OPEN csrDB
  106. FETCH NEXT
  107. FROM csrDB
  108. INTO @DBName
  109. -- ***************************************************************************
  110.  
  111. -- ***************************************************************************
  112. -- Loop through the cursor
  113. WHILE @@FETCH_STATUS = 0
  114. BEGIN
  115. -- ***************************************************************************
  116.  
  117. -- ***************************************************************************
  118. --
  119. SELECT @SQLCmd = 'INSERT ##DBUsers ' +
  120. ' SELECT ''' + @DBName + ''' AS [Database],' +
  121. ' su.[name] AS [Database User ID], ' +
  122. ' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
  123. ' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
  124. ' FROM [' + @DBName + '].[dbo].[sysusers] su' +
  125. ' LEFT OUTER JOIN ##Users u' +
  126. ' ON su.sid = u.sid' +
  127. ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
  128. ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
  129.  
  130. ' ON sm.groupuid = sug.uid)' +
  131.  
  132. ' ON su.uid = sm.memberuid ' +
  133.  
  134. ' WHERE su.hasdbaccess = 1' +
  135.  
  136. ' AND su.[name] != ''dbo'' '
  137.  
  138.  
  139.  
  140. EXEC (@SQLCmd)
  141.  
  142. -- ***************************************************************************
  143.  
  144.  
  145.  
  146. -- ***************************************************************************
  147. -- Get the next database name
  148. FETCH NEXT
  149. FROM csrDB
  150. INTO @DBName
  151. -- ***************************************************************************
  152.  
  153. -- ***************************************************************************
  154. -- End of the cursor loop
  155. END
  156. -- ***************************************************************************
  157.  
  158. -- ***************************************************************************
  159. -- Close and deallocate the CURSOR
  160. CLOSE csrDB
  161. DEALLOCATE csrDB
  162. -- ***************************************************************************
  163.  
  164. -- ***************************************************************************
  165. -- Return the Database User data
  166. SELECT *
  167. FROM ##DBUsers
  168. ORDER BY [SERVER LOGIN], [DATABASE ROLE]
  169. -- ***************************************************************************
  170.  
  171. GO
  172. -- ***************************************************************************
  173. -- Clean up - delete the Global temp tables
  174. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type IN (N'U'))
  175. DROP TABLE ##Users
  176.  
  177. GO
  178. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type IN (N'U'))
  179. DROP TABLE ##DBUsers
  180. -- ***************************************************************************
  181.  
  182. GO

Report this snippet  

You need to login to post a comment.