object privileges

  • 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

  • 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

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

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply