Document List of All Users in Database with Associated Permissions

Problem Statement

Shraddha works as an Azure PaaS DBA with Universal College Group. Developers at Universal College Group want Shraddha to document the list of all the users in the Student database along with their associated permissions.

• Azure PaaS Instance Name: universalcollege.database.windows.net
• Database Name: student

In order to accomplish this task, Shraddha decided to use Windows PowerShell Feature.

Before we start with writing down a Powershell script; we first need to install the dbatools module. In order to install the dbatools module; kindly open the Windows Powershell ISE and execute the below code:

Once the dbstools module has been loaded successfully; we will use the Export-DBAUser command to generate the list of users in the database along with their associated permissions which is as shown below:

export-dbauser -SqlInstance "universalcollege.database.windows.net" 
-Database "student" -SqlCredential "satnsing" -FilePath "D:\Permissions_Azure\Permission.sql";

where:

• sqlinstance will contain the Name of the Azure PaaS Instance.
• Database will contain the list of databases for which User Permissions Script needs to be generated. If there are multiple databases; they all need to be separated by a comma.
• sqlcredential will contain the name of the SQL User which the Powershell script will use to connect to the database. For security reasons; this feature allows us to enter the password during runtime.
• FilePath will contain the location of the .sql file which will contain the appropriate CREATE USER statement and the SQL statements for granting the appropriate permissions. We have created a file named Permission.sql onto the location named D:\Permissions_Azure.

Now let’s execute the PowerShell as shown below:

Once you execute it; System will prompt for the password for the user named “satnsing” as shown below. Kindly enter the password.

Once you enter the password; kindly press the OK button as shown above.

Once the PowerShell script gets executed successfully; the desired Information gets pulled into the file named Permission.sql as shown below:

Indeed, it’s a great PowerShell command and can be used with much ease. I hope you all found something useful to learn from this tip.

History

• 9^th June, 2021: Initial version