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

Posted by stee1rat on Stack Overflow See other posts from Stack Overflow or by stee1rat
Published on 2010-03-15T06:24:33Z Indexed on 2010/03/15 6:29 UTC
Read the original article Hit count: 181

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about database