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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Mark WillsTopic 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.