Export-DbaInstance : could required roles/permissions be mentioned in documentation?

[tivivi63]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Actually, in case the account which is executing Export-DbaInstance cmdlet is not granted sufficient permissions/roles, exceptions pop up either silently or not.

For example:

• An exception occurred while executing a Transact-SQL statement or batch (caught thanks to -EnableException_ set)_
• [Get-DbaDbMailConfig] Failure | The EXECUTE permission was denied on the object 'sysmail_help_configure_sp', database 'msdb', schema 'dbo'. (silent)
• [Get-DbaDbMailAccount] Failure | The EXECUTE permission was denied on the object 'sysmail_help_account_sp', database 'msdb', schema 'dbo'. (silent)
• [Get-DbaDbMailProfile] Failure | The EXECUTE permission was denied on the object 'sysmail_help_profile_sp', database 'msdb', schema 'dbo'. (silent)
• [Get-DbaDbMailAccount] Failure | The EXECUTE permission was denied on the object 'sysmail_help_account_sp', database 'msdb', schema 'dbo'. (silent)
• [Export-DbaLinkedServer] Not a sysadmin on xxxx\yyyyy. Quitting. (silent)
• [Export-DbaSysDbUserObject] Not a sysadmin on xxxx\yyyyy. Quitting. (silent)

At least for the last one, it looks like

$sysDbUserObjects = Export-DbaSysDbUserObject -SqlInstance $server -BatchSeparator $BatchSeparator -NoPrefix:$NoPrefix -ScriptingOptionsObject $ScriptingOption -PassThru

is not propagating any -EnableException switch value that could have been set at Export-DbaInstance-level.

In both automating pipeline and least privilege principle perspective, it is annoying to me granting sysadmin role by design, but sometimes (e.g this time) it is required, so it would be nice if you guys could mention such strong requirements in documentation, so that we can save investigation time 😏

Thanks for the great job🙏

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Please confirm that you are running the most recent version of dbatools

Sure.

PS C:\> (Get-InstalledModule dbatools).Version

Major  Minor  Build  Revision
-----  -----  -----  --------
1      1      146    -1      

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name                           Value                                                                                   
----                           -----                                                                                   
PSVersion                      5.1.19041.2364                                                                          
PSEdition                      Desktop                                                                                 
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                 
BuildVersion                   10.0.19041.2364                                                                         
CLRVersion                     4.0.30319.42000                                                                         
WSManStackVersion              3.0                                                                                     
PSRemotingProtocolVersion      2.3                                                                                     
SerializationVersion           1.1.0.1                                                                                 

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

.NET Framework Version

PSChildName                      Version       
-----------                      -------       
v2.0.50727                       2.0.50727.4927
v3.0                             3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926 
Windows Presentation Foundation  3.0.6920.4902 
v3.5                             3.5.30729.4926
Client                           4.8.04084     
Full                             4.8.04084     
Client                           4.0.0.0    

[andreasjordan]

I personally assume that the user of dbatools is a member of the sysadmin role. Especially for commands that affect the instance. Also, there may be differences between versions here that we would have to determine first. We would also have to determine the required permissions again for each new version. Therefore, I personally recommend closing the issue and not changing the documentation. However, @potatoqualitee might see it differently.

[wsmelton]

The problem with not propagating errors correctly is an issue in many commands, especially those that call other public functions because we are not handling error handling correctly.

We know many using our module are not sysadmin and use least privilege with their automation. The problem is SMO in most cases because they don't document the permissions required like T-SQL statements.

Could we, sure but it will be a huge undertaking to both keep up with it between all versions, figuring out what permission is needed at minimum, and maintaining.

[andreasjordan]

I just had a look at the code. and there are different ways to fix the missing exceptions issue.

I would suggest the following: We add -EnableException:$EnableException to all the calls to dbatools commands and put lines 211 to 416 into one big try-catch block with Stop-Function -Message "Failure" -ErrorRecord $_ -Continuein the catch block.

As this change takes some time and the diff will look very bad, I would like to first talk about this here in the issue. But I can help with coding if needed.