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

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

Answer 3

Here's how to create a list of all users of all databases in a SQL Server instance. I think that's what you're looking for.

If you want a list of all users for all databases for all instances you should be able to modify the script to do that. Just expand on the techniques used in the script.

https://tidbytez.com/2018/02/05/how-to-create-a-list-of-all-users-of-all-databases-in-a-sql-server-instance/

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sql AS VARCHAR(MAX);
DECLARE @ServerName AS SYSNAME;

SET @ServerName = (
        SELECT @@SERVERNAME
        );

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
    DROP TABLE #User
END;

CREATE TABLE #User (
    ServerName SYSNAME
    ,DbName SYSNAME
    ,UserName SYSNAME NULL
    ,LoginType VARCHAR(255) NULL
    ,Permission VARCHAR(255) NULL
    ,StateOf VARCHAR(255) NULL
    ,AccessLevel VARCHAR(255) NULL
    ,ObjectName SYSNAME NULL
    );

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
    SET @DbName = (
            SELECT MIN(DbName)
            FROM @Database
            WHERE DbName > @DbName
            )
    /*
    PUT CODE HERE
    */
    SET @sql = '
INSERT INTO #User (
    ServerName
    ,DbName
    ,UserName
    ,LoginType
    ,Permission
    ,StateOf
    ,AccessLevel
    ,ObjectName
    )
SELECT ''' + @ServerName + ''' AS ServerName
    ,''' + @DbName + ''' AS DbName
    ,princ.name AS UserName
    ,princ.type_desc AS LoginType
    ,perm.permission_name AS Permission
    ,perm.state_desc AS StateOf
    ,perm.class_desc AS AccessLevel
    ,object_name(perm.major_id) AS ObjectName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals princ
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
'

    EXEC (@sql)
END;

SELECT *
FROM #User;

DROP TABLE #User;