Enumerate the users’ rights on a DB’s objects

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.

/* 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_permissionsASDECLARE @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_typeENDCLOSE tblnamesDEALLOCATE tblnames

########################################################

This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: