PowerShell Script of the Week – Script and Compare Permissions

One of the biggest issues in compliance is finding out who has permissions to what. And once you're done with that, you need to track when that changes. PowerShell to the rescue!  Here's what I'm using for that:

   1: # Scripting database objects:
   2: # Delete the old script file.
   3: Remove-Item C:\temp\ScriptDep.sql
   4:  # Set the path context to the local, default instance of SQL Server.
   5: CD \sql\localhost\default
   6:  # Create a Scripter object and set the required scripting options.
   7: $scrp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Scripter -ArgumentList (Get-Item .)
   8: $scrp.Options.ScriptDrops = $false
   9: $scrp.Options.WithDependencies = $true
  10: $scrp.Options.IncludeIfNotExists = $true
  11:  # Set the path context to the tables in AdventureWorks2008.
  12: CD Databases\AdventureWorks2008\Tables
  13:  foreach ($Item in Get-ChildItem) { $scrp.Script($Item) | out-file -FilePath C:\temp\ScriptDep.sql -Append }

Skip to main content