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