Return DatabaseName, UserName & Role of users for a SQL Instance using sql syntax

I currently have a SQL Server Instance in the Azure platform. I have 25 different databases on the instance. I am trying to run a sql query to provide "DatabaseName","UserName","Role". Since Azure sql services are not exactly the same as normal desktop and server instances, does anyone know how to provide the fields via sql syntax?
Stacey FontenotAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
NO Do not try to create an undocumented Microsoft Stored Procedure with the same name - NEVER.

You can retrieve DB_Name() - check out : https://docs.microsoft.com/en-us/sql/t-sql/functions/db-name-transact-sql#examples-includesssdwfullincludessssdwfull-mdmd-and-includesspdwincludessspdw-mdmd and you will find a lot of functions are available. Like permissions https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql.

Maybe create a cursor to retrieve a list of DB's and then retrieve the info you want db by db. But need elastic queries.

But you should really be using Azure AD - https://docs.microsoft.com/en-us/powershell/azure/active-directory/overview?view=azureadps-1.0

There is a truckload of powershell cmdlets to help you...

Now, I am unsure of being able to transverse every DB - but pretty sure there will be a powershell script to help.

Within Azure you have to use "Elastic Queries" you can read about it : https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/ and follow the first link for more detaill - but read the whole lot first. And a good example  : https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/
1
 
Kyle AbrahamsSenior .Net DeveloperCommented:
From: https://stackoverflow.com/questions/3021755/sql-server-query-database-user-roles-for-all-databases-in-server?rq=1

Try the following:

EXECUTE sp_msforeachdb 'select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end Role
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R'''

Open in new window

0
 
Stacey FontenotAuthor Commented:
sp_msforeachdb is not available in Azure
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
Found a definition for it:

CREATE PROCEDURE [sp_MSforeachdb] 
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null, 
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null 
as 
set deadlock_priority low 

/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */ 
/* @precommand and @postcommand may be used to force a single result set via a temp table. */

/* Preprocessor won't replace within quotes so have to use str(). */ 
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12) 
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11)) 
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) 
select @dbinaccessible = N'0x80000000'	/* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

if (@precommand is not null) 
exec(@precommand)

declare @origdb nvarchar(128) 
select @origdb = db_name()

/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */ 
/* Create the select */ 
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' + 
N' where (d.status & ' + @inaccessible + N' = 0)' + 
N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )

declare @retval int 
select @retval = @@error 
if (@retval = 0) 
exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

if (@retval = 0 and @postcommand is not null) 
exec(@postcommand)

declare @tempdb nvarchar(258) 
SELECT @tempdb = REPLACE(@origdb, N']', N']]') 
exec (N'use ' + N'[' + @tempdb + N']')

return @retval

Open in new window

0
 
Stacey FontenotAuthor Commented:
Hey Kyle, currently executing the definition you have provided and I am getting

 "Msg 262, Level 14, State 18, Procedure sp_MSforeachdb, Line 1
CREATE PROCEDURE permission denied in database 'master'."

as my error. Can you explain what I'm doing wrong ?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Are you logging in as SA?  It's essentially giving you an access denied message.
0
 
Stacey FontenotAuthor Commented:
Yes I'm currently logged in, I retried logging in and executing the procedure and It is still giving the access denied message.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Apparently creation can't be done in Master as it's read-only in Azure.  Try using a different DB.
0
 
Stacey FontenotAuthor Commented:
In order to return all the DatabaseName, UserName & Role of users, wouldn't the creation have to be in master ?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
It doesn't have to be in master in order for you to read all databases.  You can also change the name to something like: custom_MSforeachdb so there's no name conflicts in the future.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Multiple answers provided.
0
 
Mark WillsTopic AdvisorCommented:
Kyle, it is quite clear (to me at least) that you are not familiar with Azure.

Unfortunately, it is NOT sql server in the cloud. It is a lot more complex than that.

Simple things like referring to another database within Azure, are not possible without "elastic queries", simple things like creating a procedure in Master are restricted.

Unfortunately there are a lot of little "gotchas" with Azure.

If I saw any part of your contributions that could share in the points, I would have done so.

Selecting your "found a definition" for a hidden MS procedure is absolutely dangerous and could damage databases. There is a reason why they have hidden the procedure, and redefining a Microsoft Procedure that ships with the product is almost inviting problems with any subsequent upgrades and honestly MUST not happen, nor suggested.

There is only one Azure centric post in this thread from the Experts....

Cheers,
Mark Wills
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.