Using SMO with Powershell

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » SMO/RMO/DMO » Using SMO with Powershell

Using SMO with Powershell

Rate Topic

Display Mode

Topic Options

Author

Message

Aaron C. Sentell

Posted Sunday, October 21, 2007 3:10 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 8:44 AM
Points: 395, Visits: 275

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

Post #413225

cmille19

Posted Tuesday, October 23, 2007 6:10 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257, Visits: 671

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"}

Post #413835

Aaron C. Sentell

Posted Friday, October 26, 2007 6:23 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 8:44 AM
Points: 395, Visits: 275

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

Post #415338

moff

Posted Wednesday, February 06, 2008 6:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 11, 2008 2:47 AM
Points: 81, Visits: 35

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

Post #452174

« Prev Topic | Next Topic »

Permissions