Query to return all users permissions on SQL Server

Question

Hi everyone,

Please how can I use TSQL to get the list of all users detailed permissions and privileges on each database.

I want the query to return critieria like:

• Username
• Database name
• Login Type (SQL, Windows user or group, etc)
• Permission Type
• Permission State
• The specified object they have privileges on and they type of privileges.
• Roles (including the permission type, state and objects for each of the roles)

I need this information in a denormalized form if possible.

Thank you.

Answer 1

Take a look at this blog post from Sebastian Meine: https://sqlity.net/en/2584/script-database-permissions/

Answer 2

Hi @tobz

Try the upvoted answer in this similar thread: SQL Server query to find all permissions/access for all users in a database.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".