Using SMO with Powershell

Question

Post reply

Using SMO with Powershell

Aaron C. Sentell

Ten Centuries

Points: 1267
More actions
October 21, 2007 at 3:10 pm

#76801

I'm new at SMO and new at Powershell. My eventual goal in the script below is to loop through all user databases on a single SQL Server and reverse engineer database object permissions to a file (one file for each database). At this point I'm trying to do this for all user tables, but I can't seem to figure out how to script out only object permissions. Can someone point me in the right direction?
Thanks,
Aaron
Here's the script so far...
---------------------------------------------------------------------------------------------------------
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$ScriptOpts = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$ScriptOpts.Permission.True
$ScriptOpts.AllowSystemObjects.False
$ScriptOpts.ContinueScriptingOnError.True
$ScriptOpts.PrimaryObject.False
$servers = "SQLServerA"
foreach ($server in $servers)
{
write-host "Details of the Server :" $server
write-host "-----------------------------------"
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$server"
write-host "Server Version: " $Server.Serverversion
write-host "Server Name: " $Server.Information.VersionString
foreach($database in $Server.databases)
{
write-host "Database:" $database.name
foreach($table in $database.tables)
{
write-host "Table: "$table.name
$table.Script()
}
}
}

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

cmille19 SSCertifiable Points: 5970 More actions · Answer 1

I would say you are new to SMO also to fix your code:

1. You misspelled permission instead of permissions and need to use the Powershell $true builtin variable:

$ScriptOpts.Permissions = $true

2. The ScriptingOptions needs to be referenced in the Script() method constructor:

$table.Script($ScriptOpts)

3. You can use a regular expression to filter out just the permission statements:

$table.Script($ScriptOpts) | where {"$_" -match "GRANT" -or "$_" -match "DENY"}

Aaron C. Sentell Ten Centuries Points: 1267 More actions · Answer 2

Thanks for your help. I haven't had time to try out your changes yet, but I did find another article that I think will be helpful to me as well from DatabaseJournal.com:

Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 9

By Muthusamy Anantha Kumar aka The MAK

Using PowerShell and SMO to Generate an SQL Server Script

http://www.databasejournal.com/features/mssql/article.php/3700711

moff SSC Enthusiast Points: 155 More actions · Answer 3

Setting PrimaryObjects to false works for scripting permissions-only for tables, views and UDFs.

However for sprocs it doesn't - the whole sproc is scripted with the permissions at the end.

Your idea of filtering the piped output is fine - except when scripting directly to file.

Am I misunderstanding what PrimaryObjects really means, or is this a bug?

BTW for completeness there should be REVOKE on your list too 🙂