Microsoft KB Archive/179158

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

Article ID: 179158

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q179158



SUMMARY
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: kbhowto KB179158

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.