Sql-server – How to get list of users on all instance’s databases? excluding the mirrors!

sql serverssmst-sql

For an audit of our MSSQL databases, I have been asked to provide a list of all users for each database running on each instance.

There is an existing stack overflow question

which advised to use…

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

However, it hits a few problems.

1) If any of the databases are a mirror, the script errors as below and doesn't return any results.

"The database "###" cannot be opened. It is acting as a mirror database."

2) If any of the databases have a special character in their name (eg. a hyphen -), again it errors and doesn't return results.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

3) The results are all in separate tables. Ideally I would like it to just output 1 table of database and users.

Can anyone help adjust the code to achieve the above?

Thanks

Best Answer

sp_MSforeachdb is undocumented, unsupported, and has some serious shortcomings. You'll need to use something else. Either writing your own cursor over sys.databases, something like this:

declare c cursor local for
    select name 
    from sys.databases d
    where name not in ('master','model','tempdb') 
    and d.state=0
declare @db sysname

open c
fetch next from c into @db
while @@fetch_status = 0
begin
  begin try
     declare @sql nvarchar(max) = concat(  N'use ', quotename(@db), N'; 
     select db_name() db, * 
     from sys.database_principals u
     where type = ''U''' 
     )

     --print @sql
     exec ( @sql )

  end try
  begin catch
    print error_message()
  end catch

  fetch next from c into @db
end

close c
deallocate c

or copying someone else's, eg here.

Related Question