locked
re: Run query on some kind of loop on all db's in server RRS feed

  • Question

  • Hi Guys

    I have written a query which returns all the object level permissions in a database.

    Need some help - i want to basically point it as say "master" but have it return all of the entries on the server for all the databases on it

    Select 
    DB_NAME() as DatabaseName,
    obj.Name as ObjectName,
    Obj.Type_Desc as ObjectType,
    usr.Name as Username,
    usr.Type_desc as TypeDesc,
    Perm.class_desc as ClassDesc,
    usr.default_schema_name as DefaultDBName,
    Perm.permission_name as PermissionType,
    Perm.state_desc
    FROM   
       sys.database_permissions AS perm
    
       --* No join to sys.objects as it excludes system objects such as extended stored procedures */
       INNER JOIN
       sys.objects AS obj
             ON perm.major_id = obj.[object_id]
       --*/
          INNER JOIN
       sys.database_principals AS usr
             ON perm.grantee_principal_id = usr.principal_id
          LEFT JOIN
       sys.columns AS cl
             ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    		 where usr.Name not in('Public','Guest')

    Please could someone help me modify my query to return all the data on the server by looping through each db except one called "DBA_Monitor" which we dont want to scan

    Thanks alot


    James :)

    Thursday, April 4, 2019 2:36 PM

Answers

  • Hi

    The first one is really good,

    is there a way to make it return as "one result set" instead of lots of individual ones?

    CREATE TABLE [master].[dbo].[privs_table](
    	[DatabaseName] [nvarchar](128) NULL,
    	[ObjectName] [sysname] NOT NULL,
    	[ObjectType] [nvarchar](60) NULL,
    	[Username] [sysname] NOT NULL,
    	[TypeDesc] [nvarchar](60) NULL,
    	[ClassDesc] [nvarchar](60) NULL,
    	[DefaultDBName] [sysname] NULL,
    	[PermissionType] [nvarchar](128) NULL,
    	[state_desc] [nvarchar](60) NULL
    );
    GO
    
    DECLARE @command varchar(1000) 
    SELECT @command = 'USE ? if DB_NAME() <> ''DBA_Monitor'' begin 
    insert into master.dbo.privs_table
    SELECT DB_NAME() AS DatabaseName,
    	obj.Name AS ObjectName,
    	Obj.Type_Desc AS ObjectType,
    	usr.Name AS Username,
    	usr.Type_desc AS TypeDesc,
    	PERM.class_desc AS ClassDesc,
    	usr.default_schema_name AS DefaultDBName,
    	PERM.permission_name AS PermissionType,
    	PERM.state_desc
    FROM sys.database_permissions AS PERM
    INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
    INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
    LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    	AND cl.[object_id] = PERM.major_id
    WHERE usr.Name NOT IN (
    		''Public'',
    		''Guest''
    		) end' 
    EXEC sp_MSforeachdb @command;
    
    select * from master.dbo.privs_table;


    • Marked as answer by James OHara Thursday, April 4, 2019 3:55 PM
    Thursday, April 4, 2019 3:50 PM
  • Thankyou for all your answers

    Was able to adapt it in the end as shown:


    All answers were good but especially loved the last one the most - cheers guys

    Cool :)
    • Marked as answer by James OHara Thursday, April 4, 2019 8:57 PM
    Thursday, April 4, 2019 5:54 PM

All replies

  • Like this:

    DECLARE @command varchar(1000) 
    SELECT @command = 'USE ? if DB_NAME() <> ''DBA_Monitor'' begin SELECT DB_NAME() AS DatabaseName,
    	obj.Name AS ObjectName,
    	Obj.Type_Desc AS ObjectType,
    	usr.Name AS Username,
    	usr.Type_desc AS TypeDesc,
    	PERM.class_desc AS ClassDesc,
    	usr.default_schema_name AS DefaultDBName,
    	PERM.permission_name AS PermissionType,
    	PERM.state_desc
    FROM sys.database_permissions AS PERM
    INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
    INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
    LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    	AND cl.[object_id] = PERM.major_id
    WHERE usr.Name NOT IN (
    		''Public'',
    		''Guest''
    		) end' 
    EXEC sp_MSforeachdb @command;


    • Edited by Lokesh Vij Thursday, April 4, 2019 2:41 PM
    Thursday, April 4, 2019 2:39 PM
  • Hi

    The first one is really good,

    is there a way to make it return as "one result set" instead of lots of individual ones?

    DECLARE @command varchar(1000) 
    SELECT @command = 'USE ? if DB_NAME() <> ''DBA_Monitor'' begin SELECT DB_NAME() AS DatabaseName,
    	obj.Name AS ObjectName,
    	Obj.Type_Desc AS ObjectType,
    	usr.Name AS Username,
    	usr.Type_desc AS TypeDesc,
    	PERM.class_desc AS ClassDesc,
    	usr.default_schema_name AS DefaultDBName,
    	PERM.permission_name AS PermissionType,
    	PERM.state_desc
    FROM sys.database_permissions AS PERM
    INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
    INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
    LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    	AND cl.[object_id] = PERM.major_id
    WHERE usr.Name NOT IN (
    		''Public'',
    		''Guest''
    		) end' 
    EXEC sp_MSforeachdb @command;

    Thursday, April 4, 2019 3:07 PM
  • This script generate script that how grant  object level permission 

    DECLARE @sql nvarchar(max) 
    SELECT @sql =
    'USE ? if DB_NAME() <> ''DBA_Monitor'' begin 
    
    SELECT  CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
    	+ SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
    	+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
    	+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
    	+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS ''--Object Level Permissions''
    FROM	sys.database_permissions AS perm
    	INNER JOIN
    	sys.objects AS obj
    	ON perm.major_id = obj.[object_id]
    	INNER JOIN
    	sys.database_principals AS usr
    	ON perm.grantee_principal_id = usr.principal_id
    	LEFT JOIN
    	sys.columns AS cl
    	ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    ORDER BY perm.permission_name ASC, perm.state_desc ASC
    end' 
    EXEC sp_MSforeachdb @sql;
    

    Hope it can help you.

     

    Best Regards,

    Natig


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 4, 2019 3:24 PM
  • Hi

    The first one is really good,

    is there a way to make it return as "one result set" instead of lots of individual ones?

    CREATE TABLE [master].[dbo].[privs_table](
    	[DatabaseName] [nvarchar](128) NULL,
    	[ObjectName] [sysname] NOT NULL,
    	[ObjectType] [nvarchar](60) NULL,
    	[Username] [sysname] NOT NULL,
    	[TypeDesc] [nvarchar](60) NULL,
    	[ClassDesc] [nvarchar](60) NULL,
    	[DefaultDBName] [sysname] NULL,
    	[PermissionType] [nvarchar](128) NULL,
    	[state_desc] [nvarchar](60) NULL
    );
    GO
    
    DECLARE @command varchar(1000) 
    SELECT @command = 'USE ? if DB_NAME() <> ''DBA_Monitor'' begin 
    insert into master.dbo.privs_table
    SELECT DB_NAME() AS DatabaseName,
    	obj.Name AS ObjectName,
    	Obj.Type_Desc AS ObjectType,
    	usr.Name AS Username,
    	usr.Type_desc AS TypeDesc,
    	PERM.class_desc AS ClassDesc,
    	usr.default_schema_name AS DefaultDBName,
    	PERM.permission_name AS PermissionType,
    	PERM.state_desc
    FROM sys.database_permissions AS PERM
    INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
    INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
    LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    	AND cl.[object_id] = PERM.major_id
    WHERE usr.Name NOT IN (
    		''Public'',
    		''Guest''
    		) end' 
    EXEC sp_MSforeachdb @command;
    
    select * from master.dbo.privs_table;


    • Marked as answer by James OHara Thursday, April 4, 2019 3:55 PM
    Thursday, April 4, 2019 3:50 PM
  • Thankyou for all your answers

    Was able to adapt it in the end as shown:

    			DECLARE @Obj VARCHAR(4000)
    			DECLARE @T_Obj TABLE (
    			ServerName VARCHAR(255),
    			Databasename VARCHAR(255),
    			ObjectName VARCHAR(255),
    			ObjectType VARCHAR(255), 
    			UserName SYSNAME, 
    			TypeDesc varchar(255), 
    			ClassDesc varchar(255), 
    			DefaultDBName varchar(255), 
    			PermissionType varchar(255))
    			SET @Obj='
    						Select
    						@@Servername AS ServerName,
    						DB_NAME() as DatabaseName,
    						obj.Name as ObjectName,
    						Obj.Type_Desc as ObjectType,
    						usr.Name as Username,
    						usr.Type_desc as TypeDesc,
    						Perm.class_desc as ClassDesc,
    						usr.default_schema_name as DefaultDBName,
    						Perm.state_desc + '' '' + Perm.permission_name as PermissionType
    						
    						FROM   
    						   sys.database_permissions AS perm
    						   INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id]
    						   INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
    						   LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    						   WHERE usr.Name not in(''Public'',''Guest'')'
    			INSERT @T_Obj 
    			EXEC sp_MSforeachdb @Obj
    			SELECT * FROM @T_Obj 

    All answers were good but especially loved the last one the most - cheers guys

    Thursday, April 4, 2019 3:55 PM
  • Thankyou for all your answers

    Was able to adapt it in the end as shown:


    All answers were good but especially loved the last one the most - cheers guys

    Cool :)
    • Marked as answer by James OHara Thursday, April 4, 2019 8:57 PM
    Thursday, April 4, 2019 5:54 PM