How to get a list of users for all instance's databases

Question

I guess the procedure should be something like this:

declare @db varchar(100)
declare @user varchar(100)
declare c cursor for select name from sys.sysdatabases        

open c

fetch next from c into @db

while @@fetch_status = 0
begin
    print @db   
    exec ('use ' + @db)

    declare u cursor for select name from sys.sysusers
        where issqlrole <> 1 and hasdbaccess <> 0 and isntname <> 1

    open u   

    fetch next from u into @user

    while @@fetch_status = 0
    begin
        print @user
        fetch next from u into @user
    end

    print '--------------------------------------------------'
    close u     
    deallocate u    
    fetch next from c into @db
end

close c
deallocate c

But the problem is that exec ('use ' + @db) doesn't work. And i always get user list of currently chosen database. How should i fix that?

P.S.: I want this code to work on both 2000 and 2005 sql servers.

Answer 1

You could also use the undocumented but well used sp_MSforeachdb stored proc - see here for details or see another blog post here:

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

The "?" is the placeholder for the database name that will be added to the command, as it gets executed against each database in your system.