:::: MENU ::::

List permissions on SQL Server 2000 objects through sp_helpProtect()

dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures, T-SQL Scripts

List permissions on SQL Server 2000 objects through sp_helpProtect()

I was required to list all permissions on a DB or table. Although this can be done through EM/SSMS but the analysis of permissions is more efficient through T-SQL than by any other means. I have found some very use full T-SQL stored procedure sp_helpProtect in this regard. It lists users permissions for an object.
General syntax for sp_helpProtect is

 
sp_helprotect [ [ @name = ] 'object_statement' ]  
 [ , [ @username = ] 'security_account' ]  
 [ , [ @grantorname = ] 'grantor' ]  
 [ , [ @permissionarea = ] 'type' ]  

You may use it in following ways

 
--List all user permissions of all Database objects  
 sp_helprotect  
 GO  
 -- List all user permissions of a table  
 sp_helprotect 'tableNme'  
 GO  
 -- List all user permissions of stored procedure  
 EXEC sp_helprotect 'spName'  
 GO  
 -- List all user permissions of sp granted by dbo  
 sp_helprotect 'spName', NULL,dbo  
 GO  
 -- List all Object type user permissions  
 sp_helprotect NULL, NULL,NULL,'o'  
 GO  
 -- List all statement type user permissions  
 sp_helprotect NULL, NULL,NULL,'s'  
 GO  
 -- List all permissions for a user  
 sp_helprotect NULL,'user'  
 GO  

Note: Basically sp_helprotect is for SQL Server 2000.For SQL Server 2005 and later sp_helprotect has no information regarding securables that were introduced in SQL Server 2005. Read here about List the permissions on SQL Server 2005 objects by using sys.database_permissions and fn_builtin_permissions instead.

  • Anonymous

    I did not get any help here for listing all permissions for a specific user in a database.

  • Atif Shehzad

    Thanks for pointing this, i have added
    — List all permissions for a user
    sp_helprotect NULL,'user'
    GO

    Such reports are easy in SQL Server 2005 and later but SQL Server 2000 lacks DMVs and other objects for monitoring.

  • Atif Shehzad

    Thanks for pointing this, i have added
    — List all permissions for a user
    sp_helprotect NULL,'user'
    GO

    Such reports are easy in SQL Server 2005 and later but SQL Server 2000 lacks DMVs and other objects for monitoring.

  • Atif Shehzad

    Similarly we may use following version of above mentioned sp for listing permissions of a user in SQL Server 2000 database

    sp_helprotect @username ='user'

Consult us to explore the Databases. Contact us