Imran Akram's blog

Let's talk about life, technicalities, career opportunities, current affairs and a lot more!

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

Posted on | August 12, 2008 | No Comments

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

  • Share/Bookmark
Category: SQL Server 2005
Tags: > > > > >

Comments

Leave a Reply