Find out the permission of the Current Connected User on the database using SQL Server 2005/2008

If you want to find out the permissions of the Current Connected Principal User on the database, then here's how to do so:

SELECT permission_name as Allowed

FROM fn_my_permissions('NORTHWIND', 'DATABASE')

The fn_my_permissions system function returns a list of the permissions effectively granted to the principal on a securable, in our case, the database Northwind. So running the query shown above, will display the permission of the current connected principal



Similarly to view permissions of the principal on a specific table, use this query

SELECT permission_name as Allowed

FROM fn_my_permissions('dbo.Employees', 'object')

If you want to view the permissions of a different principal user, use

EXECUTE AS LOGIN = 'USERNAME'
GO

and then execute the query.