Technical Article

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