How to determine effective permission of an SQL Server 2005 object?

You can find out the effective permissions on an SQL Server 2005 object by using a function called fn_my_permissions. Here’s what’s written in the books online.


Returns a list of the permissions effectively granted to the principal on a securable.


fn_my_permissions ( securable , 'securable_class')


Is the name of the securable. If the securable is the server or a database, this value should be set to NULL. securable is a scalar expression of type sysname. securable can be a multipart name.

Is the name of the class of securable for which permissions are listed. securable_class is a sysname. securable_class must be one of the following: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION.


I used it like this:-

USE myDatabase

Select * from fn_my_permissions (‘my_storedProc, ‘OBJECT’)

also remember that this is a table valued function so you can’t use the EXEC Statement with this


Author: Imran Akram

A .NET/SharePoint Consultant and a political enthusiast who believes in putting the feet of the elected and/or unelected officials to fire in order to get good governance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s