SQL Server 2000 – Required Permissions for Viewing All Current User Permissions

permissionssql serversql-server-2000

I'm maintaining an application that runs with SQL Server 8.0.760 SP3.

When I try to "see" the permissions of the current user I'm logged with, I do this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

However, the server returns this error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_my_permissions'.

If I try the following query: SELECT * FROM dbo.fn_my_permissions(NULL, 'SERVER');, I get the same error message.

How can I list all the current user permissions?

Best Answer

I had to write a permissions audit for some compliance report a while back and now I have integrated that report into my suite of administrative DB scripts.

I put a copy of the procedure that generates the report in pastebin for you and I verified that it works with SQL Server 2000 still.

http://pastebin.com/08krN7jf

It generates the report in a tree view with the login and default database listed first, then any server level roles, then any database roles and explicitly granted permissions.

Edited to add the actual code directly in the reply rather than rely on the pastebin link

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
Related Question