Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

object privileges Expand / Collapse
Author
Message
Posted Thursday, October 29, 2009 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 29, 2009 4:39 PM
Points: 1, Visits: 14
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
Post #811119
Posted Thursday, October 29, 2009 1:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:35 PM
Points: 33,099, Visits: 15,207
Try this: http://www.sqlservercentral.com/scripts/Permissions/64309/






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #811144
Posted Thursday, October 29, 2009 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 5,574, Visits: 24,820
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,
[user] [int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY ([user],
[inherfrom]
)
)

--Insert the inheritance base items which are the users themselves.
INSERT INTO #tmpInher ([user], [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.[user],
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 ([user], [inherfrom])
SELECT
oT.[user],
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



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #811156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse