Adding powershell requirements to individual command list documentation (Idea?)

[gregorylejeune]

This may be more my exposure thus far to powershell in combination with trying to apply the principals of minimal access first. I'll be the first to disclose my powershell script-fu is not as strong as I'd like it to be.

I have a couple of scheduled powershell scripts that connect to various SQL servers. I've come to notice that some powershell scripts run fine, while others require an elevated permission (such as local admin).

It would be great if there was additional documentation on minimal permissions required to run each of the powershell scripts available in dbatools.

Alternatively, opening it up more so to discussion:

If this is transparent to someone more versed in powershell or dbatools that this is more so a knowledge gap in powershell and you believe updating the documentation isn't needed:
I'd appreciate any feedback and links to documentation on PS you may have to share so that I may bridge the knowledge gap. I've found that because dbatools is open source, that it's an amazing resource and I'd like to contribute where I can if possible.

[niphlod]

on the SQL side of things the module doesn't enforce checking for particular permissions (except a small bunch). it's expected that you have enough permissions to do what's needed, and usually the error message will tell you clearly that you lack some permission.

As for "local admin": usually they're tied to commands operating on servers rather than sql instances, and it's totally dependant on your setup (like GPO, secpol, registry et similia). Documenting what's needed in that particular case would be cumbersome, as there are dozens of involved variables.

[gregorylejeune]

Thanks for the prompt response!

Completely understandable on the different factors that may impede running powershell scripts all together. I agree, those factors would be cumbersome to document. I wouldn’t want to recommend those for documentation.

I was going under the assumption for my initial post that everything is default values with regards to security policies (global, local, or registry). There should be a base level understanding, and documenting those gotcha’s for every skill level would be vast and not the best use of time. 100% agree.

When you said that on the SQL side of things, I probably should have elaborated a bit:
My use case is having a sql agent job launch powershell as a cmdexec using a fully qualifying file path to a powershell script instead of sqlps (as type Powershell).

I’ll look into enhancing my use of error trapping, as that sounds like a very solid recommendation for tracking down specific case by case troubleshooting scenarios.

Thanks again for your feedback!

[niphlod]

I feel that the whole "local admin" thing is just a matter of how you setup UAC. It should be responsible for the 95% of the problems.

That being said, and assuming dbatools is bug free, there's a "simple" way to tell if you need AT LEAST admins permissions on the host or sysadmin on the MSSQL:

• OS Level: any function using Test-ElevationRequirement (or Invoke-ManagedComputerCommand), or has in code "Security.Principal.WindowsBuiltInRole"
• MSSQL Level: any function using Test-SqlSa

If we were to be strict about this, we could "say" that we should refactor OS functions to all use Test-ElevationRequirement and all MSSQL functions to use Test-SqlSa if we were to find that a function NEEDS high level permissions.
Then it'll be just a matter of tagging consistently the commands and put the requirement in the synopsys.

[wsmelton]

We had discussed updating the help docs a long time ago (way before we got to 500+ commands 😁 ) on providing some link or detail on the permissions. Overall the issue with doing this (and maintaining it) is that SMO documentation does not include the section Permissions like T-SQL documentation does. SMO itself could be calling 2 views or 5 system procedures. There was no feasible way for us to detect all of that and maintain it as SMO updates.

All in all though if you take a given command and determine the base action it will take via T-SQL then you can have a quick start method of what least-privilege is required. A few examples:

• Get-DbaDatabase - while we do some extra stuff in this one at the base level SMO is going to query sys.databases, check the permissions for that view here. You can see from SMO doc here there is no viable info to go on tracking the permissions down.
• Get-DbaDbFile this one still uses the Database class in SMO but is going to the files of a database so most likely hitting sys.database_files in some shape or form. The permission for that only requires public role access to the database, ref.

You can see just from these two if you trace them on a given instance that n+ number of system views, functions, or procedures get called by SMO. The most efficient method is to simply run them on a test instance and start collecting the permission errors.

We do offer a command that can assist with this: Get-DbatoolsLog