Technical Article

Get users permissions with mapping thru role.

,

This procedure is similar to sp_helpprotect except to extends a bit on the concept with roles involved. If for instance a user has right on a TableX but they are not mapped directly to the user but instead to a role then sp_helpprotect does not tell you this. This will tell you how the user inherites a right from a role and all is in a crosstab format. This is still a work in progress and, yes I know you could accomplish thru sp_helprotect and sp_helprolemember in combination but that is not how I wanted to put this together.

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

Rate

Share

Share

Rate