InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:45:59 GMT20http://www.sqlservercentral.com/Forums/Topic811119-6-1.aspxIf what Steve has recommended does not do what you need try this:[code="sql"]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 [/code]Thu, 29 Oct 2009 13:42:53 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic811119-6-1.aspxTry this: [url]http://www.sqlservercentral.com/scripts/Permissions/64309/[/url]Thu, 29 Oct 2009 13:30:53 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic811119-6-1.aspxI need query to pull out all the objects privileges owned by the users in sql server 2000 databases. Any help is greatly appreciated. Thank YouThu, 29 Oct 2009 12:57:37 GMTjgovindaraman