MS SQL Server – fn_my_permissions

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

Examples:

A. Listing effective permissions on the server
The following example returns a list of the effective permissions of the caller on the server.

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);
GO

B. Listing effective permissions on the database
The following example returns a list of the effective permissions of the caller on the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions (NULL, ‘DATABASE’);
GO

C. Listing effective permissions on a view
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions(‘Sales.vIndividualCustomer’, ‘OBJECT’)
ORDER BY subentity_name, permission_name ;
GO

D. Listing effective permissions of another user
The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on user Wanida.

EXECUTE AS USER = ‘Wanida’;
SELECT * FROM fn_my_permissions(‘HumanResources.Employee’, ‘OBJECT’)
ORDER BY subentity_name, permission_name ;
REVERT;
GO

E. Listing effective permissions on a certificate
The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.

SELECT * FROM fn_my_permissions(‘Shipping47’, ‘CERTIFICATE’);
GO

F. Listing effective permissions on an XML Schema Collection
The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions(‘ProductDescriptionSchemaCollection’,
‘XML SCHEMA COLLECTION’);
GO

G. Listing effective permissions on a database user
The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.

SELECT * FROM fn_my_permissions(‘MalikAr’, ‘USER’);
GO

H. Listing effective permissions of another login
The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.

EXECUTE AS LOGIN = ‘WanidaBenshoof’;
SELECT * FROM fn_my_permissions(‘AdventureWorks.HumanResources.Employee’, ‘OBJECT’)
ORDER BY subentity_name, permission_name ;
REVERT;
GO

http://mirabedini.com/blog/?p=11