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.