:::: MENU ::::

Get list of all possibble permissions for a SQL Server 2005 object

  • Oct 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get list of all possibble permissions for a SQL Server 2005 object

It is interesting question that how to get all the possible permissions for SQL Server 2005 object. The object may be any table, view, function. Apart from these objects you could even get list of all possible permissions for your database or server.
Format for this T-SQL script is

 
USE DBNameHere  
 GO  
 SELECT *  
 FROM fn_my_permissions('ObjectName', 'type');  
 GO  

In above script provide DB name first. If you want to generate possible permissions for a database or server itself then provide NULL as objectname and write ‘database’ or ‘server’ as type parameter. For example to get possible server level permissions for server itself we will have following syntax

 
SELECT permission_name  
 FROM fn_my_permissions(null, 'server');  
 GO  

Similarly to get possible permissions for AdventureWorks database we have following syntax

 
USE AdventureWorks  
 GO  
 SELECT permission_name  
 FROM fn_my_permissions(null, 'database');  
 GO  

Here are some examples to get list of all possible permissions for SQL Server objects like view, table, database and server.
To get list of all possible permissions for a view [purchasing.object] in AdventureWorks database

 
USE AdventureWorks  
 GO  
 SELECT *  
 FROM fn_my_permissions('purchasing.vVendor', 'object');  
 GO  

Similarly to get list of all possible permissions for a table [production.product] in AdventureWorks database

 
USE AdventureWorks;  
 GO  
 SELECT *  
 FROM fn_my_permissions('production.product', 'object');  
 GO  

Above commands will provide you all permissions/privileges list. you may choose among these as required.

Consult us to explore the Databases. Contact us