Determine Users with Permission on an Object

,

Copy the script into a query window and run it. Change the object for the @OBJECT_ID parameter and the permission name for the @PERMISSION_NAME parameter as appropriate.

DECLARE @OBJECT_ID INT, @PERMISSION_NAME NVARCHAR(128)
SET @OBJECT_ID = OBJECT_ID('TABLE_NAME');
SET @PERMISSION_NAME = 'UPDATE';
CREATE TABLE #TEMPUSERS1 (PRINCIPAL_NAME SYSNAME, PRINCIPAL_TYPE VARCHAR(128), PRINCIPAL_ID INT);
CREATE TABLE #TEMPUSERS2 (PRINCIPAL_NAME SYSNAME, PRINCIPAL_TYPE VARCHAR(128), PRINCIPAL_ID INT);
INSERT #TEMPUSERS1
SELECT DU.NAME,DU.TYPE,DU.PRINCIPAL_ID FROM SYS.DATABASE_PERMISSIONS DP
JOIN SYS.DATABASE_PRINCIPALS DU ON DP.GRANTEE_PRINCIPAL_ID = DU.PRINCIPAL_ID
WHERE MAJOR_ID = @OBJECT_ID 
AND DP.PERMISSION_NAME = @PERMISSION_NAME

-- LOOP THROUGH UNTIL WE NO LONGER HAVE A ROLE IN THE LIST
WHILE EXISTS (SELECT 1 FROM #TEMPUSERS1 WHERE PRINCIPAL_TYPE = 'R')
 BEGIN
 -- GET MEMBERS OF ROLES FROM THE TEMPUSERS1 INTO TEMPUSERS2
 INSERT #TEMPUSERS2
 SELECT DU2.NAME,DU2.TYPE,DU2.PRINCIPAL_ID FROM #TEMPUSERS1 DP
 JOIN SYS.DATABASE_PRINCIPALS DU1 ON DP.PRINCIPAL_ID = DU1.PRINCIPAL_ID
 JOIN SYS.DATABASE_ROLE_MEMBERS DM ON DU1.PRINCIPAL_ID = DM.ROLE_PRINCIPAL_ID
 JOIN SYS.DATABASE_PRINCIPALS DU2 ON DM.MEMBER_PRINCIPAL_ID = DU2.PRINCIPAL_ID
 WHERE DU1.TYPE = 'R'; 
 -- NOW GET RID OF ROLES IN TEMPUSERS1
 DELETE FROM #TEMPUSERS1 WHERE PRINCIPAL_TYPE = 'R';
 -- PUT THE MEMBERS WE FOUND BACK IN TEMPUSERS1 FROM TEMPUSERS2
 INSERT #TEMPUSERS1 SELECT * FROM #TEMPUSERS2;
 -- EMPTY OUT TEMPUSERS2 BEFORE THE NEXT POSSIBLE LOOP
 DELETE FROM #TEMPUSERS2;
 END;


SELECT DISTINCT PRINCIPAL_NAME, PRINCIPAL_TYPE FROM #TEMPUSERS1 ORDER BY 2,1;

-- CLEANUP
DROP TABLE #TEMPUSERS1;
DROP TABLE #TEMPUSERS2;

Rate

4 (1)

Share

Share

Rate

4 (1)