4

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

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.