User Permissions on Tables, SPs, Views, etc
-
I'm trying to figure out how I can query the database to see who has what permissions on the Tables, Views, Stored Procedures, UDFs, etc. I'm trying to monitor what users have so that I can make sure that nobody has any permissions that they shouldn't have.
Any help would be great.
Thanks,
David
-
Try this:
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,
[int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY (
,
[inherfrom]
 
)
INSERT INTO #perm
(, [inherfrom])
SELECT
[uid], [uid]
FROM
sysusers
WHERE
issqlrole = 0
AND
hasdbaccess = 1
AND uid != 1
WHILE EXISTS (SELECT
p1.,
groupuid
FROM
sysmembers
INNER JOIN
#perm p1
ON
p1.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm p2
WHERE
p2. = p1.
 
 
BEGIN
INSERT INTO #perm (, [inherfrom])
SELECT
p1.,
groupuid
FROM
sysmembers
INNER JOIN
#perm p1
ON
p1.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm p2
WHERE
p2. = p1.
 
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 =
ON
[inherfrom] = grantee
INNER JOIN
sysobjects
ON
sysobjects.[id] = syspermissions.[id]
ORDER BY
[UserName],
[ObjectType],
[Object]
DROP TABLE #perm
GO
-
Wow! That's quite the script. I went through it and then ran it on a dev box to see what results I would get and I'm impressed. However, it does not give my any results on any of the users (developers) that have permissions in that database. Is there something I'm missing or can add on to get me that information or is it that my users don't technically have permissions (they're dbo's) and automatically inherit other permissions.
Thanks,
David
-
It will not list the user names of user inheriting permissions from dbo. I do not know how to tweak it to get the results you are after. Sorry....
-
I did some more digging and it actually tells me exactly what I want. It shows me that I've set up my developers in a very poor fashion
This does the trick for me and now I'm off to do some permission changes and correct my user setups.Thanks for all the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply