October 29, 2009 at 12:57 pm
I need query to pull out all the objects privileges owned by the users in sql server 2000 databases. Any help is greatly appreciated. Thank You
October 29, 2009 at 1:30 pm
October 29, 2009 at 1:42 pm
If what Steve has recommended does not do what you need try this:
CREATE PROCEDURE UDP_User_Permissions AS
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,
[int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY (,
[inherfrom]
)
)
--Insert the inheritance base items which are the users themselves.
INSERT INTO #tmpInher (, [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.,
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 (, [inherfrom])
SELECT
oT.,
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply