INF: How to Check Permissions for Every Table, View, & St. Proc.

• Microsoft SQL Server, versions 6.0 and 6.5

The following script will produce a list of every user's permission on every table, view, and stored procedure in a given database:

   DECLARE @object_name VARCHAR(30)
   DECLARE @object_type CHAR(2)
   DECLARE @check_message VARCHAR(75)

   DECLARE tnames_cursor CURSOR FOR
      SELECT name, type
            FROM sysobjects
      WHERE type IN ('U','P','V')
            ORDER BY 2 DESC

   OPEN tnames_cursor
   FETCH NEXT FROM tnames_cursor INTO @object_name, @object_type
   WHILE (@@fetch_status <> -1)
   BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
         SELECT @check_message = "Checking permissions on "
                  IF @object_type = 'U'
                     SELECT @check_message = @check_message + 'Table '
                  IF @object_type = 'V'
                     SELECT @check_message = @check_message + 'View '
            IF @object_type = 'P'
                     SELECT @check_message = @check_message + 'Stored
   Procedure '

               SELECT @check_message = @check_message +
   RTRIM(UPPER(@object_name))
               PRINT @check_message
         EXEC ("sp_helprotect " + @object_name )
            END
            FETCH NEXT FROM tnames_cursor INTO @object_name, @object_type
   END
   CLOSE tnames_cursor
   DEALLOCATE tnames_cursor