none
query for listing all DBs with all users by DB

    Question

  • Hello,

    I am in the process of cleaning up (with much precautions) "bad" or unneeded permissions on abandoned production SQL server with multiple DBs.

    I fixed some real "holes" already. And generally it looks and does better now.

    To continue, I would like to have a QUERY for getting a list of all DBs with all users on each of them.

    Tried couple of found on web. Some fail in the middle, some not works at all.

    Could somebody provide a query that will be able to accomplish. Sure I can click on each DB, but may be there is a nice query able to do the job.

    Thx.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, March 10, 2017 2:48 PM

Answers

  • You can use a query like below for that

    use master
    go
    IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #T
    CREATE TABLE #T (DBName varchar(100),UserName varchar(100))
    INSERT #t
    EXEC sp_MSforeachdb 'select ''?'' as dbname,name as username from ?.sys.database_principals where is_fixed_role = 0 and ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')'
    SELECT * FROM #T
    

    If you want you can use type column to further filter on types of logins like SQL_USER etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by pob579 Friday, March 10, 2017 4:00 PM
    Friday, March 10, 2017 3:27 PM
  • Does this get what you're after?

    DECLARE @DB_USers TABLE
    (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
     
    INSERT @DB_USers
    EXEC sp_MSforeachdb
     
    '
    use [?]
    SELECT ''?'' AS DB_Name,
    case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
    prin.type_desc AS LoginType,
    isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
    FROM sys.database_principals prin
    LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
    WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
    prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
     
    SELECT 
    dbname,username ,logintype ,create_date ,modify_date ,
     STUFF(
    (
     SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
     FROM @DB_USers user2
     WHERE
     user1.DBName=user2.DBName AND user1.UserName=user2.UserName
     FOR XML PATH('')
     )
     ,1,1,'') AS Permissions_user
     FROM @DB_USers user1
     GROUP BY
     dbname,username ,logintype ,create_date ,modify_date
     ORDER BY DBName,username

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, March 10, 2017 3:33 PM
    • Marked as answer by pob579 Friday, March 10, 2017 4:14 PM
    Friday, March 10, 2017 3:31 PM
  • With regards to the syntax error you get, this is an artefact of the formatting in the forums. You can fix it in this way. Press Ctrl-H to get the find/replace dialog. Click in the upper text box, that is the text to replace. Press the Alt key and keep it pressed while you type 0160 on the numeric keypad. Lift the Alt key and move to the lower text box where type a single space with the regular space key.

    The issue is that the forums has a lot of &nbsp;, or no-break space, which is not recongnized as white space by SQL 2008 and earlier versions. (I am very happy that Microsoft addressed this is SQL 2012 as I copy and paste a lot of SQL code from the forums to SSMS!)

    • Marked as answer by pob579 Tuesday, March 14, 2017 11:26 AM
    Monday, March 13, 2017 10:56 PM

All replies

  • You can use a query like below for that

    use master
    go
    IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #T
    CREATE TABLE #T (DBName varchar(100),UserName varchar(100))
    INSERT #t
    EXEC sp_MSforeachdb 'select ''?'' as dbname,name as username from ?.sys.database_principals where is_fixed_role = 0 and ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')'
    SELECT * FROM #T
    

    If you want you can use type column to further filter on types of logins like SQL_USER etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by pob579 Friday, March 10, 2017 4:00 PM
    Friday, March 10, 2017 3:27 PM
  • Does this get what you're after?

    DECLARE @DB_USers TABLE
    (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
     
    INSERT @DB_USers
    EXEC sp_MSforeachdb
     
    '
    use [?]
    SELECT ''?'' AS DB_Name,
    case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
    prin.type_desc AS LoginType,
    isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
    FROM sys.database_principals prin
    LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
    WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
    prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
     
    SELECT 
    dbname,username ,logintype ,create_date ,modify_date ,
     STUFF(
    (
     SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
     FROM @DB_USers user2
     WHERE
     user1.DBName=user2.DBName AND user1.UserName=user2.UserName
     FOR XML PATH('')
     )
     ,1,1,'') AS Permissions_user
     FROM @DB_USers user1
     GROUP BY
     dbname,username ,logintype ,create_date ,modify_date
     ORDER BY DBName,username

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, March 10, 2017 3:33 PM
    • Marked as answer by pob579 Friday, March 10, 2017 4:14 PM
    Friday, March 10, 2017 3:31 PM
  • Visakh,

    your query qave the results. Had some errors. BTW, just interesting (not for further troubleshooting) what was/could  cause those errors (see below). May be because of mix of English and French OS/SQL/Collations.

    As mentioned the server is really out of management.

    Phil,

    your query is a "KILLER"! Everything is there... and no errors. MUST HAVE thing (I think).

    Thanks.

    ********************

    Messages from Visakh query:

    Msg 102, Level 15, State 1, Line 1

    Syntaxe incorrecte vers '-'.

    Msg 102, Level 15, State 1, Line 1

    Syntaxe incorrecte vers '-'.

    Msg 102, Level 15, State 1, Line 1

    Syntaxe incorrecte vers '2015'.

    Msg 102, Level 15, State 1, Line 1

    Syntaxe incorrecte vers '2016'.

    Msg 102, Level 15, State 1, Line 1

    Syntaxe incorrecte vers 'System'.

    Msg 156, Level 15, State 1, Line 1

    Syntaxe incorrecte vers le mot cl 'update'.

    Msg 156, Level 15, State 1, Line 1

    Syntaxe incorrecte vers le mot cl 'where'.

    (183 row(s) affected)

    (183 row(s) affected)


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Friday, March 10, 2017 4:15 PM
    Friday, March 10, 2017 4:14 PM
  • Phil,

    could you recommend how to nicely export the results of your query. Now it scramble every info in each line.

    I tried:

    1. export as csv... no columns . Everything in sticked in each row.

    2. selected all, copied, pasted in Excel. Then used my old note for correctly open csv, it says:

    "Open the CSV file via a simple text edit / note pad editor. 2. Go to the first line and add above that line a new line with sep=, "

    Didn't help either...

    I am sure you can recommend something :)

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Friday, March 10, 2017 11:32 PM
    Friday, March 10, 2017 7:34 PM
  • Try this variation of Phil's query. Just copy and past the result to Excel.

    DECLARE @DB_USers TABLE
    (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

    INSERT @DB_USers
    EXEC sp_MSforeachdb

    '
    use [?]
    SELECT ''?'' AS DB_Name,
    case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
    prin.type_desc AS LoginType,
    isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
    FROM sys.database_principals prin
    LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
    WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
    prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

    SELECT DBName, UserName , LoginType , create_date ,modify_date ,
     STUFF(
    (
     SELECT char(9) + CONVERT(VARCHAR(500), AssociatedRole)
     FROM @DB_USers user2
     WHERE
     user1.DBName=user2.DBName AND user1.UserName=user2.UserName
     FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     ,1,1,'') AS Permissions_user
     FROM @DB_USers user1
     GROUP BY
     DBName, UserName , LoginType ,create_date ,modify_date
     ORDER BY DBName,UserName

    Friday, March 10, 2017 10:32 PM
  • Thanks Erland. Will do Monday morning.

    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, March 10, 2017 11:34 PM
  • this what I get:


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Monday, March 13, 2017 11:15 AM
  • found a simple solution on web works just fine:

    "I typically do this by simply click the upper left corner in the results grid, copy, and then paste into Excel. There is one catch, you need to go into options->query results-> SQL Server->results to grid (or text if you want to save to file for import into excel) and turn on include column headers when copying or saving the results. I find this works great".

    http://stackoverflow.com/questions/639886/from-sql-server-how-can-i-export-to-excel-with-the-column-headers


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Monday, March 13, 2017 11:56 AM
  • With regards to the syntax error you get, this is an artefact of the formatting in the forums. You can fix it in this way. Press Ctrl-H to get the find/replace dialog. Click in the upper text box, that is the text to replace. Press the Alt key and keep it pressed while you type 0160 on the numeric keypad. Lift the Alt key and move to the lower text box where type a single space with the regular space key.

    The issue is that the forums has a lot of &nbsp;, or no-break space, which is not recongnized as white space by SQL 2008 and earlier versions. (I am very happy that Microsoft addressed this is SQL 2012 as I copy and paste a lot of SQL code from the forums to SSMS!)

    • Marked as answer by pob579 Tuesday, March 14, 2017 11:26 AM
    Monday, March 13, 2017 10:56 PM
  • Thanks for clarifying.

    As mentioned above I used the tip found on web for adjusting query results settings in SSMS options.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, March 14, 2017 11:26 AM