SP's
-
I have several DB's (Test, dev etc) with hundreds of SP's.
What query can I use to list all the SP's that dont have 'execute' permissions for a certain group?
TIA
Carl.
-
Hi Carl,
quote:
What query can I use to list all the SP's that dont have 'execute' permissions for a certain group?maybe this one will help you. You may have to customise a little to fit your needs
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
oT.,
groupuid
FROM
sysmembers
INNER JOIN
#perm oT
ON
oT.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm iT
WHERE
iT. = oT.
)
)
BEGIN
INSERT INTO #perm (, [inherfrom])
SELECT
oT.,
groupuid
FROM
sysmembers
INNER JOIN
#perm oT
ON
oT.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm iT
WHERE
iT. = oT.
)
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
GO
sysobjects.[id] = syspermissions.[id]
ORDER BY
[UserName],
[ObjectType],
[Object]
DROP TABLE #permCheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Copy 'n' paste isn't as simple as it may seem
Here's the corrected version:
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
--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
#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
GOsorry, for the confusion
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply