Get database details with VIEW ANY DEFINITION and Powershell

Question:I would like to use Powershell script to read a database information on my server. How can I do this?

Answer:To read the information of a database, you can first grant the VIEW ANY DEFINITION permission to the current login.

SQL Server BOL defines :"VIEW ANY DEFINITION lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. Futher, permissions such CONTROL and SELECT are required"

VIEW ANY DEFINITION is at server scope. For example:

GRANT VIEW ANY DEFINITION TO test

Go

Then you can read a database information with the Powershell script like the following:

$instance = "SERVER1\test"
$dbname = "master"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object Microsoft.SqlServer.Management.SMO.Server($instance)
$db = $srv.Databases[$dbname]
$db.logfiles|measure-object -property size -sum
$logsize = ($db.logfiles|measure-object -property Size -sum).Sum/1024
$datasize = $db.Size - $logsize
write-host "log size:" $logsize
write-host "data size:" $datasize
$srv.ConnectionContext.Disconnect()