All Object Permissions
This script will create a stored procedure that displays all tables, views, functions, and stored procedure (i.e. all securable user objects) alongside which users and roles have explicit permissions on those objects, plus what those permissions are.
SCRIPT:
All Object Permissions
/********************************************************************************************************** This script will create a stored procedure that displays all tables, views, functions, and stored procedure (i.e. all securable user objects) alongside which users and roles have explicit permissions on those objects, plus what those permissions are. By default, this will list all securables that have permissions assigned. Parameters can be passed to this stored procedure to filter on what objects, type of objects and permissions you're looking to list. An interactive Reporting Services report to pass parameters and display the results of this stored proc has also been provided as an .rdl file inside the .zip'ed file you downloaded from The Lytic Group. You can add that report to any SSRS project in Visual Studio or that you can deploy directly to a SQL Server Reporting Services server. Please let us know your feedback on this script at info@lyticgroupcom ***********************************************************************************************************/ /****************************************************************** Object: StoredProcedure [dbo].[uspListObjectPermissions] Script Date: 06/18/2020 Origin: Edward Heraux, The Lytic Group (Brooklyn, USA) ******************************************************************/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[uspAllObjectPermissions] @ObjectType varchar(20)=NULL, @ObjectName sysname=NULL, @Permission varchar(20)=NULL, @PermissionState varchar(8)=NULL AS SELECT @ObjectType=ISNULL(@ObjectType,'%'),@ObjectName=ISNULL(@ObjectName,'%'),@Permission=ISNULL(@Permission,'%'),@PermissionState=ISNULL(@PermissionState,'%') SELECT OB.Name AS ObjectName ,CASE OB.xtype WHEN 'U' THEN 'Table' WHEN 'P' THEN 'Stored Proc' WHEN 'V' THEN 'View' WHEN 'FN' THEN 'Function' END AS ObjectType ,PE.class_desc AS DatabaseOrObject, PE.Major_id AS ObjectID ,PE.permission_name AS Permission, PE.state_desc AS PermissionState, PE.grantee_Principal_id AS PrincipalID ,PR.Name AS PrincipalName, PR.type_desc AS PrincipalType, PR.default_schema_name AS DefaultSchema ,PR.Modify_date AS DateModified , CASE PR.is_fixed_role WHEN 0 THEN 'Not Fixed Role' WHEN 1 THEN 'Fixed Role' END AS FixedRole --There are only 3 tables necessary to assemble all permissions on all objects and the principals to whom they were given FROM Sys.database_permissions AS PE INNER JOIN sys.sysobjects OB ON PE.Major_id=OB.id INNER JOIN sys.database_principals AS PR ON PR.principal_id=PE.grantee_Principal_id --Exclude system tables WHERE OB.xtype <>'S' AND CASE OB.xtype WHEN 'U' THEN 'Table' WHEN 'P' THEN 'Stored Proc' WHEN 'V' THEN 'View' WHEN 'FN' THEN 'Function' END LIKE @ObjectType AND OB.Name LIKE @ObjectName AND PE.permission_name LIKE @Permission AND PE.state_desc LIKE @PermissionState ORDER BY ObjectName, PrincipalName /******************************************************************************** *This table-valued function returns one row with info about the current server. This is included strictly to feed a header in the report included in your download. **********************************************************************************/ IF OBJECT_ID (N'dbo.ufnServerInfo', N'IF') IS NOT NULL DROP FUNCTION dbo.ufnServerInfo GO ALTER FUNCTION dbo.ufnServerInfo() /****************The Lytic Group, copyright, 2021*****************/ /****************Published June, 2020 *****************/ /*********from http://www.lyticgroup.com by Edward Heraux*****************/ RETURNS TABLE AS RETURN --Grab server name and version info SELECT SERVERPROPERTY('servername') AS ServerName , @@Version as SQLServerVersion , CONVERT(VARCHAR(100), serverproperty('Edition')) AS Edition , SERVERPROPERTY('productversion')AS Version , SERVERPROPERTY ('productlevel') AS ProductLevel ,CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) AS LicenseType GO SELECT * FROM dbo.ufnServerInfo()