Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Enumerate the users' rights on a DB's objects

Total Hit ( 2234)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


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


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.