Home > SQL Server, T-SQL > My Permissions System Function – SQL Server

My Permissions System Function – SQL Server


fn_my_permissions() : This function returns the permissions granted to the principal on a securable. This function returns table value as given below.

pic1

Function Syntax

fn_my_permissions ( securable , ‘securable_class’ )

Function Arguments

Securable: This is a sysname type argument. This value can be a NULL or multipart object name.

securable_class: The valid securable classes are given below

pic2Experiment

1. Get the list of permission for the current database and a table

SQL Query:

USE AdventureWorks2012;

–permissions on the database
select * from sys.fn_my_permissions(null, ‘DATABASE’)
GO

–effective permissions on a table
SELECT * FROM fn_my_permissions(‘[HumanResources].[Department]’, ‘OBJECT’)

Output:

pic3

Advertisements
  1. No comments yet.
  1. April 24, 2013 at 3:00 pm

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

%d bloggers like this: