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.

fn_my_permissions 

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

Syntax

fn_my_permissions ( securable , 'securable_class')

Arguments

securable
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.

securable_class
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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s