Looping through all DBs to find db roles/users with DDL permissions

Question

Post reply

Looping through all DBs to find db roles/users with DDL permissions

lan2022nguyen

SSC-Addicted

Points: 459

February 16, 2023 at 1:18 pm

#4150497

I have the following script to search for 1 DB to find the roles/users with DDL permissions:

USE DB1

SELECT DB_NAME() AS 'DBName'

      ,p.[name] AS 'PrincipalName'

      ,p.[type_desc] AS 'PrincipalType'

      ,p2.[name] AS 'GrantedBy'

      ,dbp.[permission_name]

      ,dbp.[state_desc]

      ,so.[Name] AS 'ObjectName'

      ,so.[type_desc] AS 'ObjectType'

  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so

    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p

    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2

    ON dbp.[grantor_principal_id] = p2.[principal_id]

 

       where dbp.[permission_name] IN ('ALTER','CREATE','DROP','TRUNCATE')

Please could someone help me to modify my script to loop through for all my databases?

This is the results:

Screen Shot 2023-02-16 at 8.20.26 AM

This topic was modified 1 year, 9 months ago by lan2022nguyen.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Take a look at the function sp_msforeachdb, and see if you can make your code work within that to iterate over each database to pull the information you wish.

Additionally you may look at Brent Ozar's First Responder Kit and the procedure there sp_ineachdb as an alternative to sp_msforeachdb.

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 2

something like this:

CREATE TABLE #tempdblogin 
  (DBName nvarchar (100), 
  PrincipalName nvarchar(100),
  PrincipalType nvarchar(100),
  GrantedBy nvarchar(100),
  permission_name nvarchar(100),
  state_desc nvarchar(100),
  ObjectName nvarchar(100),
  ObjectType nvarchar(100)
  )

EXEC sp_MSforeachdb 'USE ? insert into #tempdblogin 


SELECT DB_NAME() AS "DBName"
      ,p.[name] AS "PrincipalName"
      ,p.[type_desc] AS "PrincipalType"
      ,p2.[name] AS "GrantedBy"
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS "ObjectName"
      ,so.[type_desc] AS "ObjectType"
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

where dbp.[permission_name] IN ("ALTER","CREATE","DROP","TRUNCATE")



' 

SELECT * FROM #tempdblogin
order by dbname

DROP table #tempdblogin

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

That looks good.

You don't need to store it in a temp table if you are dropping it straight away, it will display the result set from each database in its own grid in SSMS, but it depends on what your using the output for I guess.

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 4

right, temp table is optional but i feel it's a cleaner look result. whereas the grid is hard to see. thank you