Friday, September 30, 2016

How to check my permission on SQL Server object

How do you check and see whether you have permission on a specific object and what permission types you have been granted? This requirement comes whenever you experience a permission issue and it might not be easy to get relevant information from relevant department or person. However, SQL Server offers a function for us: fn_my_permissions. This function can be used for seeing all permissions you have been granted on a particular object;

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
USE AdventureWorks2014; GO
SELECT * FROM fn_my_permissions('Sales.SalesOrderHeader', 'OBJECT') ;

This function accepts securable and securable class. There are multiple classes such as DATABASE, SCHEMA, OBJECT etc.

No comments: