Results 1 to 2 of 2

Thread: SSMS 2005, Sql 2000, get all database user's roles

  1. #1

    Thread Starter
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

    SSMS 2005, Sql 2000, get all database user's roles

    I'm using SSMS 2005 connecting to a Sql Server 2000 server. What I'm needing to do is get all of the roles for each DB user for each database on the server. There are a few hundred users each with different roles on the different databases, it would take forever to make an Excel file to record this info if I have to open each person for each database so I'm hoping there's a quick way to do this.

    Here's a screenshot of what I'm needing:
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  2. #2

    Thread Starter
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

    Re: SSMS 2005, Sql 2000, get all database user's roles

    I think I might have it:
    Code:
    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 DBName as [Database], [Name], GroupName, LoginName
        FROM #TUser
    	WHERE GroupName <> 'public'
        ORDER BY DBName, [Name], GroupName
    
        DROP TABLE #TUser
    END
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width