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

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'''

Select allOpen in new window

sp_msforeachdb is not available in Azure

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

Select allOpen in new window

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 ?

Are you logging in as SA?  It's essentially giving you an access denied message.

Yes I'm currently logged in, I retried logging in and executing the procedure and It is still giving the access denied message.

Apparently creation can't be done in Master as it's read-only in Azure.  Try using a different DB.

In order to return all the DatabaseName, UserName & Role of users, wouldn't the creation have to be in master ?

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.

Multiple answers provided.

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