Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7,SS2K
Expertise: Intermediate
May 5, 2001

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_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

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

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

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

Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date