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.

Answer 2

Here is a nice query from http://www.sqlservercentral.com/scripts/Administration/63841/ If you do not have an account, it is a free signup and a very nice resource.

Puts everything in a temp table, then you can do whatever you want with it.

USE MASTER
GO

BEGIN
    IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
        IF EXISTS (SELECT TOP 1 *
                    FROM Tempdb.sys.objects (nolock)
                    WHERE name LIKE '#TUser%')
            DROP TABLE #TUser
    ELSE
        IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
            IF EXISTS (SELECT TOP 1 *
                        FROM Tempdb.dbo.sysobjects (nolock)
                        WHERE name LIKE '#TUser%')
                DROP TABLE #TUser

        CREATE TABLE #tuser 
        (
            ServerName varchar(256),
            DBName SYSNAME,
            [Name] SYSNAME,
            GroupName SYSNAME NULL,
            LoginName SYSNAME NULL,
            default_database_name SYSNAME NULL,
            default_schema_name VARCHAR(256) NULL,
            Principal_id INT,
            sid VARBINARY(85)
        )

        IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
            INSERT INTO #TUser
            EXEC sp_MSForEachdb
            '
            SELECT 
            @@SERVERNAME,
            ''?'' as DBName,
            u.name As UserName,
            CASE 
            WHEN (r.uid IS NULL) THEN ''public''
            ELSE r.name
            END AS GroupName,
            l.name AS LoginName,
            NULL AS Default_db_Name,
            NULL as default_Schema_name,
            u.uid,
            u.sid
            FROM [?].dbo.sysUsers u
            LEFT JOIN ([?].dbo.sysMembers m 
            JOIN [?].dbo.sysUsers r
            ON m.groupuid = r.uid)
            ON m.memberuid = u.uid
            LEFT JOIN dbo.sysLogins l
            ON u.sid = l.sid
            WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
            /*and u.name like ''tester''*/
            ORDER BY u.name
            '
        ELSE IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
            INSERT INTO #TUser
            EXEC sp_MSForEachdb
            '
            SELECT 
            @@SERVERNAME,
            ''?'',
            u.name,
            CASE 
            WHEN (r.principal_id IS NULL) THEN ''public''
            ELSE r.name
            END GroupName,
            l.name LoginName,
            l.default_database_name,
            u.default_schema_name,
            u.principal_id,
            u.sid
            FROM [?].sys.database_principals u
            LEFT JOIN ([?].sys.database_role_members m
            JOIN [?].sys.database_principals r 
            ON m.role_principal_id = r.principal_id)
            ON m.member_principal_id = u.principal_id
            LEFT JOIN [?].sys.server_principals l
            ON u.sid = l.sid
            WHERE u.TYPE <> ''R''
            /*and u.name like ''tester''*/
            order by u.name
            '

    SELECT *
    FROM #TUser
    ORDER BY DBName, [name], GroupName

    DROP TABLE #TUser
END