Change way how Get-DbaDbFile and Get-DbaDbFileMapping get data

[olegstrutinskii]

Summarize Functionality

When the database status is not Normal, let's query the system view master.sys.master_files instead of reporting an error.

WARNING: [08:52:52][Get-DbaDbFile] Failure | Database '<databaseName>' cannot be opened. It is in the middle of a restore.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

No response

[wsmelton]

Querying sys.master_files would require more permissions than the command requires currently.

[olegstrutinskii]

We are database administrators, we have all the necessary rights 💯 we always need dba rights to do the database thing =)

[17:35:49] C:\Users\me> import-module E:\GitHub\sqlcollaborative\dbatools.psd1

Just CONNECT TO SERVER right

[17:36:58][28,26 s] C:\Users\me> Get-DbaDbFile -SqlInstance SRV-SQLSR -Database SSISDB
WARNING: [17:37:16][Get-DbaDbFile] Failure | The server principal "MyDomain\me" is not able to access the
database "SSISDB" under the current security context.

Just CONNECT TO SERVER and CONNECT DATABASE rights

[17:37:36][15,62 ms] C:\Users\me> Get-DbaDbFile -SqlInstance SRV-SQLSR -Database SSISDB
<no output at all>

Just CONNECT TO SERVER, CONNECT DATABASE and VIEW ANY DEFINITION rights

[17:37:40][789,26 ms] C:\Users\me> Get-DbaDbFile -SqlInstance SRV-SQLSR -Database SSISDB

ComputerName InstanceName SqlInstance Database FileGroupName ID Type TypeDescription LogicalName PhysicalName
------------ ------------ ----------- -------- ------------- -- ---- --------------- ----------- ------------
SRV-SQLSR    MSSQLSERVER  SRV-SQLSR   SSISDB                  2    1 LOG             log         K:\MSSQL\DATA\MSSQL...
SRV-SQLSR    MSSQLSERVER  SRV-SQLSR   SSISDB   PRIMARY        1    0 ROWS            data        K:\MSSQL\DATA\MSSQL...

[wsmelton]

You cannot make an assumption that (1) every user of our module is a DBA nor (2) has the explicit permissions to perform their job in any given environment.

The command as-is if we assume SMO only queries sys.database_files only requires public role to the database. Adding a query to sys.master_files will require one of the following explicit permissions on all databases:

• CREATE DATABASE
• ALTER ANY DATABASE
• VIEW ANY DEFINITION

If the executing user of the command is missing permission the query, as a whole, will fail to execute.

[olegstrutinskii]

Yes, sorry, wrong assumptions. Then check if the user has the necessary rights, in case the database is inaccessible?

[wsmelton]

Updated, worded wrong as those are instance level permissions.

[olegstrutinskii]

The proposed change to Get-DbaDbFile may be very useful in a scenario where the database is restored with some randomness in file names:

• 04:00 - First Job - FULL DB_COPY Restore
  • We have dynamic file names supplied here with help of DestinationFileSuffix and current date and time - $destinationFileSuffix = "_NIGHT_$(Get-Date -Format 'yyyyMMddHHmm')"
  • NoRecovery - $True
• 08:00 - Second Job DIFF backup was taken, restore it to DB_COPY
  • In this moment we cant get file names to supply to Restore-DbaDatabase with the FileMapping parameter At the last step Restore-DbaDatabase just did some work and did non restore DIFF.

Rare situation due to random database file names.