Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Get database details with VIEW ANY DEFINITION and Powershell

09 June,2012 by Tom Collins

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:



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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Get database details with VIEW ANY DEFINITION and Powershell | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer