How To Check User Privileges in SQL Server

Due to the principle of “minimum authority for maximum security”, we should regularly check which user is authorized on the sql server, and revoke the unnecessary privileges from the users. In this article I will share scripts related to the following levels of authority.

  • Server Level Authorized Users(Logins)
  • Database LevelAuthorized Users(Logins)
  • Schema Level Authorized Users(Logins)
  • Object Level Authorized Users(Logins)

Server Level Authorized Users(Logins)

Database LevelAuthorized Users(Logins)

If you are using the always on availability group, it returns results for primary databases and also standalone databases.

Schema Level Authorized Users(Logins)

If you are using the always on availability group, it returns results for primary databases and also standalone databases.

Object Level Authorized Users(Logins)

You can see these privileges for the single database with the following script.

If you are using the always on availability group, you can use the below script for primary databases of availabilityg groups and also standalone databases on the instance.