MSSQL T-SQL to return all the login, return all the DB user and their permission

Question

Recently I have a audit project, need quried all the logins and its permissions. for example, if the login has sysadmin, it break policy and need to decrease the permission

Answer 1

Thanks for your question. 

Please find reference document for queries listing all permission. 

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#examples 

Similar thread and T-SQL Commands -

https://learn.microsoft.com/en-us/answers/questions/466580/azure-sql-server-security 

Let us know if this helps!

 

Thank you!