InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:30:14 GMT20http://www.sqlservercentral.com/Forums/Topic413225-22-1.aspxSetting 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 :)Wed, 06 Feb 2008 06:55:43 GMTmoffhttp://www.sqlservercentral.com/Forums/Topic413225-22-1.aspxThanks 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 9By Muthusamy Anantha Kumar aka The MAKUsing PowerShell and SMO to Generate an SQL Server Script[url=http://www.databasejournal.com/features/mssql/article.php/3700711][u]http://www.databasejournal.com/features/mssql/article.php/3700711[/u][/url]Fri, 26 Oct 2007 06:23:28 GMTAaron C. Sentellhttp://www.sqlservercentral.com/Forums/Topic413225-22-1.aspxI 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 = $true2. 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"}Tue, 23 Oct 2007 06:10:22 GMTcmille19http://www.sqlservercentral.com/Forums/Topic413225-22-1.aspxI'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,AaronHere'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() } }}Sun, 21 Oct 2007 15:10:00 GMTAaron C. Sentell