/**********************************************************************************************************



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