Get database details with VIEW ANY DEFINITION and Powershell

09 June,2012 by Jack Vamvas

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()

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on Get database details with VIEW ANY DEFINITION and Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer