Binaryworld - Enumerate the users' rights on a DB's objects ... [ SQL Server -> Users-Logins-Rights ]

SQL Server provides several security levels, and the control that the system administrators can pursue over the DB's objects is extremely accurate. It's often useful to know which rights each user has. As you might know already, a lot of administrative SQL Server tasks can be performed through the proper stored procedures. In this case the sp_helprotect stored procedure returns the information regarding the rights that the users own over the current database.

The drawback is that the sp_helprotect stored procedure only works on a single object. The following stored procedure, sp_get_object_permissions, takes advantage of a cursor to create a report about the security level implemented by the current DB's table, view, and stored procedure objects. Modifying it by introducing a parameter that lets to specify the database that you are interested in is a simple task.

Click here to copy the following block

/* SP sp_get_object_permissions:
enumerate the rights of each user over the active DB's tables, views and stored procedures
Author : Giuseppe Dimauro
*/
CREATE PROCEDURE sp_get_object_permissions
AS

DECLARE @obj_name VARCHAR(30)
DECLARE @obj_type CHAR(2)
DECLARE @message VARCHAR(75)
DECLARE tblnames CURSOR FOR
  SELECT name, type
   FROM sysobjects
  WHERE type IN ('U','P','V')
   ORDER BY 2 DESC

OPEN tblnames
FETCH NEXT FROM tblnames INTO @obj_name, @obj_type
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
BEGIN
  IF @obj_type = 'U'
   SELECT @message = 'Checking the rights for the Table '
  IF @obj_type = 'V'
   SELECT @message = ' Checking the rights for the Vable '
  IF @obj_type = 'P'
   SELECT @message = ' Checking the rights for the Stored Procedure '
   SELECT @message = @message + RTRIM(UPPER(@obj_name))
   PRINT @message
   EXEC ('sp_helprotect ' + @obj_name )
END
FETCH NEXT FROM tblnames INTO @obj_name, @obj_type
END
CLOSE tblnames
DEALLOCATE tblnames