查看...所有当前用户权限需要什么权限？

前一段时间我不得不为一些合规报告编写权限审计，现在我已经将该报告集成到我的管理数据库脚本套件中。

我为您在 pastebin 中放置了生成报告的过程的副本，并且我验证了它仍然可以与 SQL Server 2000 一起使用。

http://pastebin.com/08krN7jf

它在树视图中生成报告，首先列出登录名和默认数据库，然后是任何服务器级别的角色，然后是任何数据库角色和明确授予的权限。

编辑直接在回复中添加实际代码而不是依赖 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

感谢这个线程：“如何在 sql server 2000 中检查登录用户权限”，我设法查看了当前用户权限。

我运行以下查询：

选择
  o.name 为 [table]，
  --u.name 作为 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 [插入],
  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]
来自系统用户
inner join sysobjects o on ( o.xtype in ( 'V', 'U' ) and o.Name NOT LIKE 'dt%' and o.Name NOT LIKE 'sys%' )
在 p1.uid = u.uid 和 p1.id = o.id 和 p1.action = 193 上左加入 sysprotects 作为 p1
在 p2.uid = u.uid 和 p2.id = o.id 和 p2.action = 195 上左加入 sysprotects 作为 p2
在 p3.uid = u.uid 和 p3.id = o.id 和 p3.action = 197 上左加入 sysprotects 作为 p3
在 p4.uid = u.uid 和 p4.id = o.id 和 p4.action = 196 上左加入 sysprotects 作为 p4
在 p5.uid = u.uid 和 p5.id = o.id 和 p5.action = 224 上左加入 sysprotects 作为 p5
左连接（
  选择
    case when is_member('db_denydatareader')=1 then 0 --explicit deny
         当 is_member('db_datareader')=1 或 is_member('db_owner')=1 时 1
    以读者身份结束，--write permissions 不给你阅读的权限
    case when is_member('db_denydatareader')=1 --显式允许
           或 is_member('db_denydatawriter')=1 然后 0 --explicit deny
         当 (is_member('db_datareader')=1 或 is_member('db_owner')=1)
          和 (is_member('db_datawriter')=1 或 is_member('db_owner')=1) 然后 1
         否则为空
    end as writer——如果你没有读权限，你就没有写权限
) u.uid=0 上的固定角色
其中（u.name=SYSTEM_USER
    或存在（从 sysusers gu 中选择 1，其中 name=SYSTEM_USER 和 u.uid=gu.gid）
    或者 u.uid=0 --public 角色
  )
  并且（合并（p1.action，p2.action，p3.action，p4.action，p5.action）不为空
    或 coalesce(fixedroles.reader, fixedroles.writer) 不为空
  )
按 o.name 分组