List What Permissions Each Database Role or User Has in a Database

Below is another script I frequently use to list what users or roles have what permissions to tables and views in our database. It basically cursors through the list of objects and stores the results of sp_helprotect in a temp table then displays the results.


DECLARE helpprotect CURSOR READ_ONLY
FOR
SELECT name
FROM sysobjects
WHERE type IN ( 'U','V')

DECLARE @name SYSNAME
OPEN helpprotect

CREATE TABLE #helprotect
    (
      Owner sysname,
      Object sysname,
      Grantee sysname,
      Grantor sysname,
      ProtectType NVARCHAR(10),
      ACTION NVARCHAR(20),
      [COLUMN] sysname
    )

FETCH NEXT FROM helpprotect INTO @name
WHILE ( @@fetch_status <> -1 )
    BEGIN
        IF ( @@fetch_status <> -2 )
            BEGIN
                INSERT  INTO #helprotect
                        EXEC sp_helprotect @name
            END
        FETCH NEXT FROM helpprotect INTO @name
    END

CLOSE helpprotect
DEALLOCATE helpprotect
GO

SELECT  *
FROM    #helprotect

DROP TABLE #helprotect

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.