How to get a list of users for all instance's databases
- by stee1rat
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.
