INF: How to Check Permissions for Every Table, View, and Stored Procedure

ID: Q179158

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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

Additional query words: permissions users security st proc stproc

Keywords : SSrvGen
Version : WINNT: 6.0 6.5
Platform : winnt
Issue type : kbhowto