View effective permission of single user

Login to SQL Server as an admin account. Run following query by impersonating the user;

execute as user = 'SomeUserName' -- Set this to the user name you wish to check
select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName
order by subentity_name, permission_name

This will list all effective permission for this user;

FavoriteLoadingAdd to favorites
Spread the love

Author: Shahzad Khan

Software developer / Architect