List all logins, databases, role mapping

  • EVCCDBA

    Old Hand

    Points: 347

    Hi there,

    I'm looking for a query or script that can generate a list of all SQL Server login's on an instance, along with all the roles they are mapped to on what databases. I'm new to SQL Server and am unaware of what system views or tables I can use to provide me with this information. It would be nice if all this info is contained somewhere in the master database instead of having to go to each database individually. I'm using SQL Server 2005.

    Thanks!

  • Howard C. BAchtel-438731

    SSCrazy

    Points: 2384

    I actually logged in today to ask just about the same question. I would also like to know how this can be done. The sp_helprotect will provide a list of tables to groups (or individuals if applicable).

    I believe some combination of sys.database_principals, sys.database_permissions and sys.database_role_members will give the user > Role ID list. However I have not been able to determine how to link the user to the role.

    As for me also, any help would be appreciated.

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    Try this:

    To get a list of SQL Server logins:

    [font="Courier New"]EXEC master..sp_helplogins[/font]

    A list of Server role memberships:

    [font="Courier New"]EXEC master..sp_helpsrvrolemember[/font]

    To get a list of database users, role memberships for all databases:

    [font="Courier New"]DECLARE @name sysname,

    @sql nvarchar(4000),

    @maxlen1 smallint,

    @maxlen2 smallint,

    @maxlen3 smallint

    IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')

    DROP TABLE #tmpTable

    CREATE TABLE #tmpTable

    (

    DBName sysname NOT NULL ,

    UserName sysname NOT NULL,

    RoleName sysname NOT NULL

    )

    DECLARE c1 CURSOR for

    SELECT name FROM master.sys.databases

    OPEN c1

    FETCH c1 INTO @name

    WHILE @@FETCH_STATUS >= 0

    BEGIN

    SELECT @sql =

    'INSERT INTO #tmpTable

    SELECT N'''+ @name + ''', a.name, c.name

    FROM [' + @name + '].sys.database_principals a

    JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id

    JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id

    WHERE a.name != ''dbo'''

    EXECUTE (@sql)

    FETCH c1 INTO @name

    END

    CLOSE c1

    DEALLOCATE c1

    SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)

    FROM #tmpTable

    SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)

    FROM #tmpTable

    SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)

    FROM #tmpTable

    SET @sql = 'SELECT LEFT(DBName, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'LEFT(UserName, ' + LTRIM(STR(@maxlen2)) + ') AS ''User Name'', '

    SET @sql = @sql + 'LEFT(RoleName, ' + LTRIM(STR(@maxlen3)) + ') AS ''Role Name'' '

    SET @sql = @sql + 'FROM #tmpTable '

    SET @sql = @sql + 'ORDER BY DBName, UserName'

    EXEC(@sql)[/font]

    You can lookup each of the stored procs & views in BOL for more details on each.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Howard C. BAchtel-438731

    SSCrazy

    Points: 2384

    Perfect. Thank you.

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    No problem. That query could be trimmed a bit. I just pulled it out of a documentation script I have that outputs the results wrapped in HTML, so it includes formatting to remove extraneous spaces.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • swekik

    Old Hand

    Points: 386

    Hi scott,I have some prolem regarding deleting the logins that dont have any acess to the databases.We r having some 600 logins are there,its very difficult to go to each login and delete,Is there any script to delete the logins which doesn't have any database acess??

    Thanks in advance.

  • alan.m.howarth

    SSC Enthusiast

    Points: 134

    Scott,

    Do you happen to have a SQL2000 version of this script? I changed sys.databases to dbo.sysdatabases, but still receive errors.

    Thanks,

    Al

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    Alan,

    What errors?

    swekik, you need to run through the databases and query against sys.logins and use left joins to find those that aren't matched up. How many databases do you have?

    I could do

    select l.principal_id, d1.name, d2.name

    from master.sys.server_principals l

    left outer join Adventureworks.sys.database_principals d1

    on l.sid = d1.sid

    left outer join TestDB.sys.database_principals d2

    on l.sid = d2.sid

    where d1.name is null

    and d2.name is null

    and repeat that for each database if I didn't have many. If you had a lot, I might tackle it differently, but this is quick and dirty looking for IDs that aren't in there. I'd also make sure these aren't groups/roles in there since I didnt' check for that. This should get you a good list.

  • alan.m.howarth

    SSC Enthusiast

    Points: 134

    Scott,

    The errors are:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbMaintenance.sys.database_principals'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbMaintenance.sys.database_role_members'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbMaintenance.sys.database_principals'.

    Msg 208, Level 16, State 1, Line 1

    This is repeated for each Database.

    Thanks,Al

  • Fandango68

    SSC Rookie

    Points: 31

    thecosmictrickster@gmail.com - Wednesday, February 20, 2008 6:53 PM

    Try this:To get a list of SQL Server logins:[

    Great little script. May I suggest you place a state = 0 at the end of sys.databases, to only retrieve ONLINE databases? Otherwise the entire script fails.

    SELECT name FROM master.sys.databases WHERE state = 0    --only ONLINE databases

    Cheers

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply