CODEWITHSUNDEEP
t-sqlazure-sql-databaseazure-sql-server
Richard D Lawson
Richard D Lawson

Reputation: 335

TSQL: List all users in all databases attached to an Azure SQL Server

I know I can go to each individual database and run:

SELECT name AS username FROM sys.sysusers

And that gets me a list of users for that Azure SQL Database.

And from the master DB on the Azure SQL Server, I can run:

SELECT name AS databasename FROM dbo.sysdatabases

But what I can't figure out for Azure is how to loop through all the databases and, for each database, list its users.

I obviously can't create a loop for USE @databasename since USE doesn't work for Azure SQL Databases.

The best I can do is write code to create code. I looped through the databases and printed out a string to run sqlcmd for each database. Obviously not ideal, but it was the best I could come up with.

Does anyone have any better suggestions for looping through all the Azure SQL Databases on an Azure SQL Server and running commands on each database?

Upvotes: 2

Views: 1528

Answers (1)

Mike Ubezzi
Mike Ubezzi

Reputation: 1027

It is the case that you will need to iterate through each database to run your desired T-SQL but, I do have a query example that you might find useful.

Since you are looking to get a list of all users on a per database basis and not necessarily looking for login and user occurrences per database, this script will also provide you the username value as well as the authenitcation_type and type values pertaining to the method of authentication and the principal type of the user. This information is super beneficial as organizations move away from traditional SQL Login/User type accounts to managed directory principles and identity groups. This scenario may or may not apply in your case but I thought I would share:

select name as username,
   create_date,
   modify_date,
   type_desc as type,
   authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R')
   and sid is not null
order by username;

This example and additional information can be found on a blog authored by [Bart Gawrych1 that is appropriately titled: List users in Azure SQL database

Note: His example excludes Type = X where I have modified this to include External_User or External_Group user type values....just for awareness.

Regards, Mike

Upvotes: 1

Related Questions