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?