SHARE
TWEET

SQL All Users All Databases

cbossie Dec 27th, 2019 245 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @Output TABLE
  2. (database_name nvarchar(100), UserName nvarchar(100), UserType nvarchar(100), databseusername nvarchar(100), db_role nvarchar(100), permission_type nvarchar(100), permission_state nvarchar(100), object_type nvarchar(100), objectName nvarchar(100), columnname nvarchar(100))
  3.  
  4. DECLARE @DatabaseName nvarchar(100)
  5.  
  6. DECLARE @DB TABLE (db nvarchar(100))
  7. INSERT @DB
  8. SELECT name from sys.databases WHERE STATE_DESC = 'ONLINE'
  9.  
  10.  
  11. DECLARE @Sql nvarchar(max)
  12. SET @Sql = '
  13. DECLARE @Output TABLE
  14. (database_name nvarchar(100), UserName nvarchar(100), UserType nvarchar(100), databseusername nvarchar(100), db_role nvarchar(100), permission_type nvarchar(100), permission_state nvarchar(100), object_type nvarchar(100), objectName nvarchar(100), columnname nvarchar(100));
  15.  
  16.  
  17.  
  18.  
  19. ' + CHAR(13)+CHAR(10)
  20.  
  21.  
  22. DECLARE dbc CURSOR FOR SELECT db FROM @DB
  23. OPEN dbc
  24. FETCH NEXT FROM dbc into
  25. @DatabaseName
  26.  
  27.  
  28. WHILE @@FETCH_STATUS = 0
  29. BEGIN
  30.    
  31.  
  32.  
  33. print @databaseName
  34.  
  35.  
  36. SET @Sql = @SQL + '
  37.  
  38.  
  39. INSERT @OUTPUT
  40. SELECT  
  41.     '''+@DatabaseName +''' AS DB,
  42.    [UserName] = CASE princ.[type]
  43.                    WHEN ''S'' THEN princ.[name]
  44.                    WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
  45.                 END,
  46.    [UserType] = CASE princ.[type]
  47.                    WHEN ''S'' THEN ''SQL User''
  48.                    WHEN ''U'' THEN ''Windows User''
  49.                 END,  
  50.    [DatabaseUserName] = princ.[name],      
  51.    [Role] = null,      
  52.    [PermissionType] = perm.[permission_name],      
  53.    [PermissionState] = perm.[state_desc],      
  54.    [ObjectType] = obj.type_desc,
  55.    [ObjectName] = OBJECT_NAME(perm.major_id),
  56.    [ColumnName] = col.[name]
  57. FROM    
  58.    '+ @DatabaseName + '.sys.database_principals princ  
  59. LEFT JOIN
  60.    '+ @DatabaseName + '.sys.login_token ulogin on princ.[sid] = ulogin.[sid]
  61. LEFT JOIN        
  62.    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
  63. LEFT JOIN
  64.    '+ @DatabaseName + '.sys.columns col ON col.[object_id] = perm.major_id
  65.                    AND col.[column_id] = perm.[minor_id]
  66. LEFT JOIN
  67.    '+ @DatabaseName + '.sys.objects obj ON perm.[major_id] = obj.[object_id]
  68. WHERE
  69.    princ.[type] in (''S'',''U'')
  70. UNION
  71.  
  72. SELECT  
  73.     '''+@DatabaseName +''' AS DB,
  74.    [UserName] = CASE memberprinc.[type]
  75.                    WHEN ''S'' THEN memberprinc.[name]
  76.                    WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
  77.                 END,
  78.    [UserType] = CASE memberprinc.[type]
  79.                    WHEN ''S'' THEN ''SQL User''
  80.                    WHEN ''U'' THEN ''Windows User''
  81.                 END,
  82.    [DatabaseUserName] = memberprinc.[name],  
  83.    [Role] = roleprinc.[name],      
  84.    [PermissionType] = perm.[permission_name],      
  85.    [PermissionState] = perm.[state_desc],      
  86.    [ObjectType] = obj.type_desc,
  87.    [ObjectName] = OBJECT_NAME(perm.major_id),
  88.    [ColumnName] = col.[name]
  89. FROM    
  90.    '+ @DatabaseName + '.sys.database_role_members members
  91. JOIN
  92.    '+ @DatabaseName + '.sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
  93. JOIN
  94.    '+ @DatabaseName + '.sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
  95. LEFT JOIN
  96.    '+ @DatabaseName + '.sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
  97. LEFT JOIN        
  98.    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
  99. LEFT JOIN
  100.    '+ @DatabaseName + '.sys.columns col on col.[object_id] = perm.major_id
  101.                    AND col.[column_id] = perm.[minor_id]
  102. LEFT JOIN
  103.    '+ @DatabaseName + '.sys.objects obj ON perm.[major_id] = obj.[object_id]
  104. UNION
  105. SELECT  
  106.     '''+@DatabaseName +''' AS DB,
  107.    [UserName] = ''{All Users}'',
  108.    [UserType] = ''{All Users}'',
  109.    [DatabaseUserName] = ''{All Users}'',      
  110.    [Role] = roleprinc.[name],      
  111.    [PermissionType] = perm.[permission_name],      
  112.    [PermissionState] = perm.[state_desc],      
  113.    [ObjectType] = obj.type_desc,
  114.    [ObjectName] = OBJECT_NAME(perm.major_id),
  115.    [ColumnName] = col.[name]
  116. FROM    
  117.    '+ @DatabaseName + '.sys.database_principals roleprinc
  118. LEFT JOIN        
  119.    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
  120. LEFT JOIN
  121.    '+ @DatabaseName + '.sys.columns col on col.[object_id] = perm.major_id
  122.                    AND col.[column_id] = perm.[minor_id]                  
  123. JOIN
  124.    '+ @DatabaseName + '.sys.objects obj ON obj.[object_id] = perm.[major_id]
  125. WHERE
  126.    roleprinc.[type] = ''R'' AND
  127.    roleprinc.[name] = ''public'' AND
  128.    obj.is_ms_shipped = 0
  129. ORDER BY
  130.    princ.[Name],
  131.    OBJECT_NAME(perm.major_id),
  132.    col.[name],
  133.    perm.[permission_name],
  134.    perm.[state_desc],
  135.    obj.type_desc;
  136.    
  137.  
  138.  
  139.  
  140.  
  141. ' + CHAR(13)+CHAR(10)
  142.  
  143.  
  144.  
  145.    
  146.     FETCH NEXT FROM dbc into @DatabaseName
  147. END
  148.  
  149. CLOSE dbc
  150. DEALLOCATE dbc
  151.  
  152. SET @SQL = @SQL + ' SELECT * FROM @Output; '
  153.  
  154. exec( @SQL )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top