Que permissão eu preciso para visualizar... todas as permissões de usuário atuais?

Tive que escrever uma auditoria de permissões para algum relatório de conformidade há algum tempo e agora integrei esse relatório ao meu conjunto de scripts de banco de dados administrativos.

Coloquei uma cópia do procedimento que gera o relatório no pastebin para vocês e verifiquei que funciona com SQL Server 2000 ainda.

http://pastebin.com/08krN7jf

Ele gera o relatório em uma exibição em árvore com o login e o banco de dados padrão listados primeiro, depois quaisquer funções de nível de servidor, depois quaisquer funções de banco de dados e permissões explicitamente concedidas.

Editado para adicionar o código real diretamente na resposta, em vez de depender do link pastebin

CREATE PROCEDURE [dbo].[spAdmin_PermissionsReport]
AS
BEGIN
    /*
        Description:    A report of each login in a database server along with its
                corresponding server roles and the databases that the login has access to.
                For each database, the database roles and explicit permissions in that
                database are also listed.

                The report is generated in the following format:

                ServerName
                    LoginName [DefaultDB]
                    Created
                    Full Name

                    ServerRole(s):
                        ServerRole1
                        ServerRole2
                        ...
                    Database(s):
                        db1
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        db2
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        ...
    */

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON

    DECLARE
        @SQL                VARCHAR(8000)
    ,   @DB                 VARCHAR(128)
    ,   @LoginName          VARCHAR(128)
    ,   @DefaultDB          VARCHAR(128)
    ,   @CreateDate         DATETIME
    ,   @MemberSID          VARBINARY(85)
    ,   @UID                SMALLINT
    ,   @ServerRole         VARCHAR(128)
    ,   @DatabaseRole       VARCHAR(128)
    ,   @Permission         VARCHAR(200)
    ,   @FirstServerRole    BIT
    ,   @FirstDatabase      BIT
    ,   @FirstDatabaseRole  BIT
    ,   @FirstPermission    BIT
    ,   @RecordSeparator    VARCHAR(1000)

    -- The 'first' variables flag when permission headers should be printed, 1 means yes.
    SELECT
        @FirstServerRole    = 1
    ,   @FirstDatabase      = 1
    ,   @FirstDatabaseRole  = 1
    ,   @FirstPermission    = 1
    ,   @RecordSeparator    = '================================================================================'

    CREATE TABLE #t_Logins (
        LoginName       VARCHAR(128)     NULL
    ,   MemberSID       VARBINARY(85)    NULL
    ,   DefaultDB       VARCHAR(128)     NULL
    ,   CreateDate      DATETIME     NULL
    )

    CREATE TABLE #t_Databases (
        DBName          VARCHAR(128)    NOT NULL
    )

    -- Roles for a server
    CREATE TABLE #t_SRVRoleMember (
        ServerRole      VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    )

    -- Roles for the current database
    CREATE TABLE #t_RoleMember (
        DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NULL
    )

    -- All database roles by database
    CREATE TABLE #t_DBRoleMember (
        DBName          VARCHAR(128)    NOT NULL
    ,   DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    ,   UID         SMALLINT    NOT NULL
    )

    -- Permissions for a database
    CREATE TABLE #t_Protect (
        Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    )

    -- All permissions by database
    CREATE TABLE #t_DBProtect (
        DBName          VARCHAR(128)    NOT NULL
    ,   Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    ,   UID         SMALLINT    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL            
    )

    -- Populate server logins
--  PRINT 'Populate Server Logins'
    INSERT INTO #t_Logins
        SELECT
            sl.[name]
        ,   sl.SID
        ,   sl.DBName
        ,   sl.CreateDate
        FROM
            master.dbo.SYSLOGINS sl
        WHERE
            [name] IS NOT NULL
        AND SID IS NOT NULL
        ORDER BY
            [name]

    -- Populate the databases
--  PRINT 'Populate Databases'
    INSERT INTO #t_Databases
        SELECT
            d.[name]
        FROM
            master..sysdatabases d
        WHERE
            d.[name] NOT IN ( 'tempdb', 'model' )
        AND HAS_DBACCESS(d.[name])                          = 1
AND d.name = 'TMPSERVICE'
        ORDER BY
            d.[name]


    -- Populate logins' server roles
--  PRINT 'Populate Logins'' Server Roles'
    INSERT INTO #t_SRVRoleMember
        EXEC sp_HelpSRVRoleMember


    -- Populate logins' database roles
    DECLARE cDBs CURSOR FOR
        SELECT
            DBName
        FROM
            #t_Databases
        ORDER BY
            DBName

    OPEN cDBs

    FETCH NEXT FROM cDBs INTO @DB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get the current database role
        SET @SQL = 'INSERT INTO #t_RoleMember EXEC [' + @DB + '].dbo.sp_helprolemember'
--      PRINT 'Insert DB Roles for ' + @DB
        EXEC(@SQL)

        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName' +
        '   ,   ''public'' AS DBRole' +
        '   ,   tl.LoginName ' +
        '   ,   tl.MemberSID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Logins tl ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  tl.MemberSID = u.SID '
        EXEC(@SQL)

        -- Store the current database role by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' ' +
        '   ,   t.DBRole ' +
        '   ,   t.MemberName ' +
        '   ,   t.MemberSID ' +
        '   ,   u.UID ' +
        '   FROM ' +
        '       #t_RoleMember t ' +
        '       JOIN #t_Logins tl ' +
        '           ON  t.MemberSID = tl.MemberSID ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  t.MemberName = u.[name] COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       t.MemberName ' +
        '   ,   t.DBRole '
        EXEC(@SQL)

        -- purge out the current database roles for next database
        DELETE FROM #t_RoleMember

        -- Get the database permissions
        SET @SQL = 'INSERT INTO #t_Protect EXEC [' + @DB + '].dbo.sp_helprotect'
--      PRINT 'Insert Permissions for ' + @DB
        EXEC(@SQL)

        -- Store the current database permissions by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBProtect ( ' +
        '   DBName ' +
        ',  Owner ' +
        ',  Object ' +
        ',  Grantee ' +
        ',  Grantor ' +
        ',  ProtectType ' +
        ',  [Action] ' +
        ',  [Column] ' +
        ',  UID ' +
        ',  MemberSID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName ' +
        '   ,   t.Owner ' +
        '   ,   t.Object ' +
        '   ,   t.Grantee ' +
        '   ,   t.Grantor ' +
        '   ,   t.ProtectType ' +
        '   ,   t.[Action] ' +
        '   ,   t.[Column] ' +
        '   ,   u.UID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Protect t ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           JOIN #t_Logins l ' +
        '               ON  u.SID = l.MemberSID ' +
        '           ON  t.Grantee = u.Name COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       Grantee ' +
        '   ,   Object'

        EXEC(@SQL)

        -- purge out the current database permissions for next database
        DELETE FROM #t_Protect

        FETCH NEXT FROM cDBs INTO @DB
    END

    CLOSE cDBs
    DEALLOCATE cDBs

--  SELECT * FROM #t_Logins
--  SELECT * FROM #t_Databases
--  SELECT * FROM #t_SRVRoleMember
--  SELECT * FROM #t_DBRoleMember
--  SELECT * FROM #t_DBProtect

    -- GENERATE REPORT
    PRINT   'Begin report - ' + CONVERT(VARCHAR, GETDATE())
    PRINT   'Report: spAdmin_PermissionsReport'
    PRINT   'Description: A report of each login in a database server along with its'
    PRINT   'corresponding server roles and the databases that the login has access to.'
    PRINT   'For each database, the database roles and explicit permissions in that'
    PRINT   'database are listed.'
    PRINT   @RecordSeparator
    PRINT 'This report reflects local accounts only.'
    PRINT   @RecordSeparator

    SELECT  @SQL = 'SERVERNAME: ' + @@SERVERNAME
    PRINT   @SQL
    PRINT   @RecordSeparator

    DECLARE cLogins CURSOR FOR
        SELECT
            LoginName
        ,   MemberSID
        ,   DefaultDB
        ,   CreateDate
        FROM
            #t_Logins
        ORDER BY
            LoginName

    OPEN cLogins

    FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQL = CHAR(9) + 'LOGINNAME:' + CHAR(9) + @LoginName + ' [' + @DefaultDB + ']'
        PRINT @SQL
        SELECT @SQL = CHAR(9) + 'CREATED:' + CHAR(9) + CONVERT(VARCHAR, @CreateDate)
        PRINT @SQL

        DECLARE cServerRoles CURSOR FOR
            SELECT
                ServerRole
            FROM
                #t_SRVRoleMember
            WHERE
                MemberSID = @MemberSID
            ORDER BY
                ServerRole

        OPEN cServerRoles

        FETCH NEXT FROM cServerRoles INTO @ServerRole

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF ( @FirstServerRole = 1 )
            BEGIN
                PRINT ''
                SELECT @SQL = CHAR(9) + 'SERVERROLE(S):'
                PRINT @SQL
                SET @FirstServerRole = 0
            END

            SELECT @SQL = CHAR(9) + CHAR(9) + @ServerRole
            PRINT @SQL

            FETCH NEXT FROM cServerRoles INTO @ServerRole
        END

        CLOSE cServerRoles
        DEALLOCATE cServerRoles

        DECLARE cDatabases CURSOR FOR
            SELECT
                DBName
            FROM
                #t_Databases
            ORDER BY
                DBName

        OPEN cDatabases

        FETCH NEXT FROM cDatabases INTO @DB

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE cDatabaseRoles CURSOR FOR
                SELECT
                    DBRole
                FROM
                    #t_DBRoleMember
                WHERE
                    DBName = @DB
                AND MemberSID = @MemberSID
                ORDER BY
                    DBRole

            OPEN cDatabaseRoles

            FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END


                IF ( @FirstDatabaseRole = 1 )
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'DATABASEROLE(S):'
                    PRINT @SQL
                    SET @FirstDatabaseRole = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @DatabaseRole
                PRINT @SQL

                FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole
            END

            CLOSE cDatabaseRoles
            DEALLOCATE cDatabaseRoles

            DECLARE cPermissions CURSOR FOR
                SELECT
                    UPPER(LTRIM(RTRIM(dbp.ProtectType))) + ' ' + UPPER(LTRIM(RTRIM(dbp.[Action]))) + CASE WHEN dbp.[Column] NOT IN ('.', '(All+New)', '(All)') THEN ' (' + dbp.[Column] + ')' ELSE '' END + ' ON ' + dbp.Object + ' TO ' + dbp.Grantee
                FROM
                    #t_DBProtect dbp
                    JOIN #t_Logins l
                        ON  dbp.MemberSID = l.MemberSID
                WHERE
                    dbp.DBName = @DB
                AND l.MemberSID = @MemberSID

            OPEN cPermissions

            FETCH NEXT FROM cPermissions INTO @Permission

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END

                IF ( @FirstPermission = 1)
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'PERMISSION(S):'
                    PRINT @SQL
                    SET @FirstPermission = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @Permission
                PRINT @SQL

                FETCH NEXT FROM cPermissions INTO @Permission
            END

            CLOSE cPermissions
            DEALLOCATE cPermissions

            SET @FirstDatabaseRole = 1
            SET @FirstDatabase = 1
            SET @FirstPermission = 1

            FETCH NEXT FROM cDatabases INTO @DB
        END

        CLOSE cDatabases
        DEALLOCATE cDatabases

        SET @FirstDatabase = 1
        SET @FirstServerRole = 1

        PRINT @RecordSeparator

        FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate
    END

    PRINT 'End report - ' + CONVERT(VARCHAR, GETDATE())

    CLOSE cLogins
    DEALLOCATE cLogins

    DROP TABLE #t_DBProtect
    DROP TABLE #t_Protect
    DROP TABLE #t_DBRoleMember
    DROP TABLE #t_RoleMember
    DROP TABLE #t_SRVRoleMember
    DROP TABLE #t_Databases
    DROP TABLE #t_Logins
END

Graças a este tópico: 'como verificar a permissão do usuário de login no sql server 2000' , consegui ver as permissões atuais do usuário.

Eu executo a seguinte consulta:

selecionar
  o.name como [tabela],
  --u.name como through_user,
  isnull(min(CASE p1.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.reader END), 0) as [select],
  isnull(min(CASE p2.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [insert],
  isnull(min(CASE p3.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [update],
  isnull(min(CASE p4.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [delete]
de sysusers vc
sysobjects de junção interna o on ( o.xtype in ( 'V', 'U' ) e o.Name NOT LIKE 'dt%' e o.Name NOT LIKE 'sys%' )
junção esquerda sysprotects como p1 em p1.uid = u.uid e p1.id = o.id e p1.action = 193
junção esquerda sysprotects como p2 em p2.uid = u.uid e p2.id = o.id e p2.action = 195
junção esquerda sysprotects como p3 em p3.uid = u.uid e p3.id = o.id e p3.action = 197
junção esquerda sysprotects como p4 em p4.uid = u.uid e p4.id = o.id e p4.action = 196
junção esquerda sysprotects como p5 em p5.uid = u.uid e p5.id = o.id e p5.action = 224
Associação à esquerda (
  selecionar
    caso quando is_member('db_denydatareader')=1 então 0 --negação explícita
         quando is_member('db_datareader')=1 ou is_member('db_owner')=1 então 1
    termine como leitor, --write permissions não lhe dá permissões para ler
    caso quando is_member('db_denydatareader')=1 --permissão explícita
           ou is_member('db_denydatawriter')=1 então 0 --negação explícita
         quando (is_member('db_datareader')=1 ou is_member('db_owner')=1)
          e (is_member('db_datawriter')=1 ou is_member('db_owner')=1) então 1
         senão nulo
    terminar como escritor - você não tem permissões de gravação se não tiver permissões de leitura
) funções fixas em u.uid=0
onde (u.name=SYSTEM_USER
    ou existe (selecione 1 de sysusers gu onde name=SYSTEM_USER e u.uid=gu.gid)
    ou u.uid=0 --função pública
  )
  e (coalesce(p1.action, p2.action, p3.action, p4.action, p5.action) não é nulo
    ou coalesce(fixedroles.reader, fixedroles.writer) não é nulo
  )
agrupar por o.name