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

1

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
Screen Shot 2023-02-16 at 8.20.26 AM1520×236 23.4 KB

2

You can use this

declare @Command nvarchar(max) = 'Use [?]

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'')'

exec sp_MSforeachdb @command1 = @command

3

thanks mike