Click here to Skip to main content
15,867,308 members
Articles / Hosted Services / Azure
Tip/Trick

Document List of All Users in Database with Associated Permissions

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
9 Jun 2021CPOL2 min read 3.4K   4   1
How to document the list of users in a student database along with their associated permissions
In this tip, you will see how to use the Windows PowerShell feature to list users in a database with their 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:

Image 1

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:

Image 2

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

Image 3

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:

Image 4

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

  • 9th June, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect CapGemini India Private Limited
India India
Satnam Singh is a DBA Manager with Capgemini in India. Satnam has around 14 years of experience on Microsoft SQL Server Technology. His main area of expertise is T-SQL, High Availability solutions, Migrations, Upgradations, Performance Tuning etc. During his free time when he is not talking about SQL, Satnam loves spending time with his family. Satnam Lives along with his Parents in Mumbai,India.

Comments and Discussions

 
GeneralThis is marvelous explanation Pin
Ashok Chauhan 20219-Jun-21 19:41
Ashok Chauhan 20219-Jun-21 19:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.