Get users permissions with mapping thru role.

CREATE PROCEDURE sp_Permissions
AS

/*
	Note: You will see multiple Objects with the same name if the
	user has more than one inheritance for that table.
	Keep in mind that Granted superseeds revoked and denied superseeds all
	when viewing the output. Keep in mind this is still a project in
	progress and I will be adding to it. This will capture the defined
	object permissions and not things like db_owner, db_denydatareader
	and such but I paln to add this later.

	SELECT
	
	Grant, revoke, or deny SELECT permissions on this object.
	
	INSERT
	
	Grant, revoke, or deny INSERT permissions on this object.
	
	UPDATE
	
	Grant, revoke, or deny UPDATE permissions on this object.
	
	DELETE
	
	Grant, revoke, or deny DELETE permissions on this object.
	
	EXEC
	
	Grant, revoke, or deny EXECUTE permissions on this object.
	
	DRI
	
	Grant, revoke, or deny declarative referential integrity permissions on this object
*/

SET NOCOUNT ON --Don't want all the counts from the process to return

--Check for and drop our temp table if exists
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] LIKE '#tmpInher%')
	DROP TABLE #tmpInher 

--Create our temp work table to make sure we have all the inheritance
CREATE TABLE #tmpInher (
	[qid] [int] IDENTITY (1,1) NOT NULL,
	[user] [int] NOT NULL,
	[inherfrom] [int] NOT NULL,
	PRIMARY KEY (
		[user],
		[inherfrom]
	)
)

--Insert the inheritance base items which are the users themselves.
INSERT INTO #tmpInher ([user], [inherfrom]) SELECT [uid], [uid] FROM sysusers WHERE issqlrole = 0 AND hasdbaccess = 1 and uid != 1

--Loop thru until we get all the inheritance items that a user is associated with.
WHILE EXISTS (SELECT 
			oT.[user], 
			groupuid 
		FROM 
			sysmembers 
		INNER JOIN 
			#tmpInher oT 
		ON 
			oT.[inherfrom] = sysmembers.memberuid 
		WHERE 
			groupuid NOT IN (
				SELECT 
					inherfrom 
				FROM 
					#tmpInher iT 
				WHERE 
					iT.[user] = oT.[user]
			)
	)
BEGIN
	INSERT INTO #tmpInher ([user], [inherfrom])
	SELECT 
		oT.[user], 
		groupuid 
	FROM 
		sysmembers 
	INNER JOIN 
		#tmpInher oT 
	ON 
		oT.[inherfrom] = sysmembers.memberuid 
	WHERE 
		groupuid NOT IN (
			SELECT 
				inherfrom 
			FROM 
				#tmpInher iT 
			WHERE 
				iT.[user] = oT.[user]
		)
END

--Check permissions for the user from all inheritance paths.

SELECT 
	u2.[name] AS UserName,
	u1.[name] AS InheritesVia,
	CASE xtype
		WHEN 'U' THEN 'Table'
		WHEN 'V' THEN 'View'
		WHEN 'S' THEN 'System'
		WHEN 'P' THEN 'Procedure'
		WHEN 'FN' THEN 'Function'
	END AS ObjectType,
	sysobjects.[name] AS Object, 
	CASE WHEN xtype IN ('U','V','S') THEN
		CASE 
			WHEN (actadd & 1) = 1 THEN 'Granted'
			WHEN (actmod & 1) = 1 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [SELECT],
	CASE WHEN xtype IN ('U','V','S') THEN
		CASE 
			WHEN (actadd & 8) = 8 THEN 'Granted'
			WHEN (actmod & 8) = 8 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [INSERT],
	CASE WHEN xtype IN ('U','V','S') THEN
		CASE 
			WHEN (actadd & 2) = 2 THEN 'Granted'
			WHEN (actmod & 2) = 2 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [UPDATE],
	CASE WHEN xtype IN ('U','V','S') THEN
		CASE 
			WHEN (actadd & 16) = 16 THEN 'Granted'
			WHEN (actmod & 16) = 16 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [DELETE],
	CASE WHEN xtype IN ('P','FN') THEN
		CASE 
			WHEN (actadd & 32) = 32 THEN 'Granted'
			WHEN (actmod & 32) = 32 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [EXEC],
	CASE WHEN xtype IN ('U','V','S') THEN
		CASE 
			WHEN (actadd & 4) = 4 THEN 'Granted'
			WHEN (actmod & 4) = 4 THEN 'Denied'
			ELSE 'Revoked'
		END 
		ELSE ''
	END AS [DRI]
FROM 
	syspermissions
INNER JOIN 
	#tmpInher
	INNER JOIN
		sysusers u1
	ON
		u1.uid = [inherfrom]
	INNER JOIN
		sysusers u2
	ON
		u2.uid = [user]
ON 
	[inherfrom] = grantee
INNER JOIN
	sysobjects
ON
	sysobjects.[id] = syspermissions.[id]
ORDER BY
	[UserName],
	[ObjectType],
	[Object]

--Drop out temp table as we no longer need.
DROP TABLE #tmpInher

GO