Generate Role Member Reports using dbatools and the ImportExcel PowerShell modules

  • Comments posted to this topic are about the item Generate Role Member Reports using dbatools and the ImportExcel PowerShell modules

  • I did something similar to this not too long ago to get all of the users/logins for servers and their permissions for the servers and databases. Had to do it for audit purposes.  Took me some trial/error to get the commands but it was pretty similar to this. Having a post like this would have saved me some time. 🙂

  • I'm glad you found it useful Peter. Even if you are not using it right now or using your own solution. 🙂

    On the permissions field, I also encourage people to also use the "Get-DbaPermission" command and export to an Excel spreadsheet to audit/analyse the data.

  • Great article, Appreciate for explaining in details.

    One issue / bug to report :

    There's an issue with "databaselevel" report for a domain account (domain\username) when its mapped to a user without the name domain in the front. No issue otherwise when there's 1:1 same name mapping between logins to users.

    To reproduce this issue, you can run this after changing the username and domain at your choice. Third tab in excel wont show below permission details.

    USE [master]
    GO
    CREATE LOGIN [DOMAIN\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    USE [userdb]
    GO
    CREATE USER [username] FOR LOGIN [Domain\username]
    GO
    USE [userdb]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [username]
    GO

    • This reply was modified 3 years, 10 months ago by  Ash.
    • This reply was modified 3 years, 10 months ago by  Ash.
    • This reply was modified 3 years, 10 months ago by  Ash.
  • Thank you for the article.  I don't know if I have found a bug with this command or now but I clean up the disabled accounts for users prior to running this.  I have verified that they no longer exist on the server but when running this command it still shows that they have logins even though they do not.

  • Hi Robert!

    You are welcome.

    I haven't seen that problem so far. Have you reutilized the "-SqlInstance" parameter after your clean up? Or did you start one session from scratch? If the 1st one, chances are the object was not up-to-date.

Viewing 6 posts - 1 through 5 (of 5 total)

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