Generate Role Member Reports using dbatools and the ImportExcel PowerShell modules

Question

Generate Role Member Reports using dbatools and the ImportExcel PowerShell modules

Cláudio Silva

SSC Eights!

Points: 986
More actions
June 30, 2020 at 12:00 am

#3764593

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

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

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

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 1

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. 🙂

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 2

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.

Ash SSC Eights! Points: 947 More actions · Answer 3

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, 11 months ago by Ash.
This reply was modified 3 years, 11 months ago by Ash.
This reply was modified 3 years, 11 months ago by Ash.

Robert Haack SSC Veteran Points: 237 More actions · Answer 4

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.

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 5

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.