如何获取所有实例数据库的用户列表 [英] How to get a list of users for all instance's databases

问题描述

我猜程序应该是这样的:

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

但是问题是exec('use'+ @db)不起作用.而且我总是得到当前选择的数据库的用户列表.我该如何解决?

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 .:我希望此代码在2000和2005 sql服务器上都能工作.

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

推荐答案

您还可以使用未记录但使用得很好的sp_MSforeachdb存储过程-请参见

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.

这篇关于如何获取所有实例数据库的用户列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！