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