Getting full userlist from SQL.

  • Anyone know how to obtain a full userlist from a SQL Database with full permissions.

    There must be some query you can run through the Analyser.

    Any suggestions would really help.

    Many thanks

  • What about sp_helprotect or

    
    
    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
    p1.,
    groupuid
    FROM
    sysmembers
    INNER JOIN
    #perm p1
    ON
    p1.[inherfrom] = sysmembers.memberuid
    WHERE
    groupuid NOT IN (
    SELECT
    inherfrom
    FROM
    #perm p2
    WHERE
    p2. = p1.
    )
    )
    BEGIN
    INSERT INTO #perm (, [inherfrom])
    SELECT
    p1.,
    groupuid
    FROM
    sysmembers
    INNER JOIN
    #perm p1
    ON
    p1.[inherfrom] = sysmembers.memberuid
    WHERE
    groupuid NOT IN (
    SELECT
    inherfrom
    FROM
    #perm p2
    WHERE
    p2. = p1.
    )
    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
    #perm
    INNER JOIN
    sysusers u1
    ON
    u1.uid = [inherfrom]
    INNER JOIN
    sysusers u2
    ON
    u2.uid =
    ON
    [inherfrom] = grantee
    INNER JOIN
    sysobjects

    ON
    sysobjects.[id] = syspermissions.[id]
    ORDER BY
    [UserName],
    [ObjectType],
    [Object]

    DROP TABLE #perm
    GO

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    Edited by - Frank kalis on 12/05/2003 06:14:34 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • This doesn't appear to work correctly.

    I'm basically after a list of users that have been created under SECURITY|LOGINS and what Databases they have access to and what permissions.

    any ideas?

  • So, more something like sp_helplogins ?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • sp_helplogins

    sp_helpuser

    sp_helprotect

  • Thanks for that, worked perfectly.

Viewing 6 posts - 1 through 5 (of 5 total)

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