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)