Review Login permissions in SQL Server

How to check whether my user has permissions on any database using T-SQL

HAS_PERMS_BY_NAME -- Evaluates the permissions of the current user

Below query returns 1 if the user has permissions on the msdb database else 0

Use msdb
go
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Below query is to verify server level permission for the current user

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');


fn_my_permissions   -- Returns permissions of the current user on database / server / an object

Below query returns what permissions does the user has on the database

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

Below query returns what permissions does the user has on the server

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

Below query returns what permissions does the user has on the specified object (ie., table / view)

SELECT * FROM fn_my_permissions('dbo.tbl_references', 'OBJECT')
GO

Below query helps to review what are the permissions for login User3 on the current database

Use KalyanDB
EXECUTE AS USER = 'User3';
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
Revert

If you dont specify Revert then the current session will get user3 privileges only so we need to specify revert after completion of Execute as user.