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