Set The Owner Of all Objects Using SQL Server PowerShell Provider

I'm going to standardize a bit - or at least try - in this blog. On Monday's I'll share something new that I've learned to do with PowerShell - sometimes with the PowerShell Provider for SQL Server 2008, and sometimes just using PowerShell and the SMO connections.

This weekend I played around with setting the owner of all the objects in a directory. Working some time back with my good friend Sean McCown, we found that we had to refresh the objects after the permissions were set. Assuming that you are in a directory of tables or some other database object, this snippet will change the owner of all of those objects to "sa" - change the name if you want something else:

   1: # Set the owner for all objects in a directory
   2: DIR | foreach-object {$_.SetOwner('sa'); $_.Refresh()}

As always, try this on a test system and database.

Comments (0)

Skip to main content