locked
How to pull all users along with their permissions on a specific database RRS feed

  • Question

  • I want to produce worksheet below , some info like username , email etc should come from AD . anyone have script to share or any ideas to start would be helpful. it is for auditing who has what rights on db.

    DB_user         Username            Email              department       database    adgroupname       environment

    kmungary    kelly mungary kmungary@domain.com   IT            db_name          adgroup               dev


    Wednesday, July 22, 2020 5:17 AM

All replies

  • Best Regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 22, 2020 8:03 AM
  • Hi,

    Is there any update on this case?
    Please feel free to drop us a note if there is any update.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 27, 2020 1:10 AM
  • Cris,

    We need to generate a report (excel worksheet etc) in the format , I  Have two challenges  unable to get figure out.

    1, Some of the fields should come outside the database like name , email etc .

    2 i need to group the permission for that user 

    format example excel sheet

    name                                          email                          databaseusername               permissions 

    kelly mungary                          kmungary@domain.com      kmungary               db_reader, db_writer ,db_owner

    john dick                                      jdick@domain.com           jdick                           ddl_admin

    if there is any help that would be great starting point.



    Monday, July 27, 2020 5:48 PM
  • Take a look at Sebastian Meine's blog post. It may not be exactly what you are looking for, but it could be a starting point.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 27, 2020 9:24 PM
  • Hi shan2008,

    >>>1, Some of the fields should come outside the database like name , email etc .
    For data that does not belong to SQL Server, maybe you can create a table in the database and import the data, and then combine with the following query.

    >>>2 i need to group the permission for that user

    use [DBName]
    go
    SELECT 
        mmbrp.[name] as Database_User_Name,
     ul.[name] as Login_Name,
       STUFF((SELECT ', ' + rolp.[name]
              FROM  sys.database_principals rolp,
        sys.database_role_members mmbr
              WHERE rolp.[principal_id] = mmbr.[role_principal_id]
               and mmbrp.[principal_id] = mmbr.[member_principal_id]
              FOR XML PATH('')), 1, 1, '') [DB_Role]
    FROM
         sys.database_principals mmbrp,
      sys.server_principals ul 
    WHERE   ul.[sid] = mmbrp.[sid]  
    ORDER BY mmbrp.[name]

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 28, 2020 8:48 AM
  • Hi shan2008,

    Is there any update on this case? Was your issue resolved?

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 3, 2020 1:16 AM