Script to display permissions in a db

,

The script creates a stored procedure which lists all permissions on a given db on execution. Unlike other scripts it does not use a cursor. I have chosen the sp_* prefix as I have created it in master. Have fun!

IF OBJECT_ID('sp_Permissions') IS NOT NULL
	DROP PROCEDURE sp_Permissions
GO
CREATE PROCEDURE sp_Permissions
AS

SET NOCOUNT ON 

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] LIKE '#perm%')
	DROP TABLE #perm 

CREATE TABLE #perm (
	[qid] [int] IDENTITY (1,1) NOT NULL,
	[user] [int] NOT NULL,
	[inherfrom] [int] NOT NULL,
	PRIMARY KEY (
		[user],
		[inherfrom]
	)
)

INSERT INTO #perm 
	([user], [inherfrom]) 
	SELECT 
		[uid], [uid] 
	FROM 
		sysusers 
	WHERE 
		issqlrole = 0 
	AND 
		hasdbaccess = 1 
	AND uid != 1

WHILE EXISTS (SELECT 
			p1.[user], 
			groupuid 
		FROM 
			sysmembers 
		INNER JOIN 
			#perm p1 
		ON 
			p1.[inherfrom] = sysmembers.memberuid 
		WHERE 
			groupuid NOT IN (
				SELECT 
					inherfrom 
				FROM 
					#perm p2 
				WHERE 
					p2.[user] = p1.[user]
			)
	)
BEGIN
	INSERT INTO #perm ([user], [inherfrom])
	SELECT 
		p1.[user], 
		groupuid 
	FROM 
		sysmembers 
	INNER JOIN 
		#perm p1 
	ON 
		p1.[inherfrom] = sysmembers.memberuid 
	WHERE 
		groupuid NOT IN (
			SELECT 
				inherfrom 
			FROM 
				#perm p2 
			WHERE 
				p2.[user] = p1.[user]
		)
END

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 
	#perm
	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 TABLE #perm
GO

Rate

Share

Share

Rate