Check user rights
Multiple DBA’s, each with their own style of database and user management, has left my employer with a combination of users and groups across several instances containing multiple databases. I had a request to move a database from a soon-to-be-deprecated instance, and thought this would be a perfect opportunity to begin the rather daunting task of combining users with similar rights into groups. In order to insure the user experience was the same before and after moving them into an AD group, I ran the following script to verify rights:
USE nancy_test;
EXECUTE AS USER = ‘mydomain\username’;
SELECT *
FROM fn_my_permissions(NULL, ‘Database’)
ORDER BY subentity_name, permission_name ;
REVERT;
GO
Execute as user allows you to impersonate another user. Fn_my_permissions lists permissions granted to the executing user. Executing as another user requires ‘impersonate’ rights in the database.
Happy Querying!