July 8, 2003 at 8:09 am
Does anyone know of a script or software package that will list all of the sql server logins and db rights for each login?
July 8, 2003 at 8:26 am
Hi rellimj,
quote:
Does anyone know of a script or software package that will list all of the sql server logins and db rights for each login?
maybe sp_helpprotect is already what you need.
If not, try this one
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 #perm
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 8, 2003 at 8:34 am
Beautiful.
May 24, 2006 at 7:16 pm
I'm getting an error
Server: Msg 170, Level 15, State 1, Procedure sp_Permissions, Line 50
Line 50: Incorrect syntax near 'sysobjects'.
This script is way beyond my skill level, can anyone tell me what to fix to make it work?
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 #perm
May 26, 2006 at 12:48 pm
Remove the GO from this line:
ON [inherfrom] = grantee INNER JOIN sysobjects ON GO sysobjects.[id] = syspermissions.[id] ORDER BY
However I still get an empty list from running this query.
May 30, 2006 at 6:40 pm
As do I, does anyone know why that is the case?
May 30, 2007 at 11:56 am
This is what you want to get you going. The & is a bitwise operator to decipher the bitmask for security.
SELECT
so.name
, su.name
, 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 dbo.syspermissions sp
inner join dbo.sysobjects so on sp.id = so.id
inner join dbo.sysusers su on su.uid = sp.grantee
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply