Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using SMO with Powershell Expand / Collapse
Author
Message
Posted Sunday, October 21, 2007 3:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Tuesday, October 23, 2007 6:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 3:35 PM
Points: 259, Visits: 715
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
Posted Friday, October 26, 2007 6:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, February 6, 2008 6:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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 »

Add to briefcase

Permissions Expand / Collapse