This Is Why I LOVE PowerShell!

I’m working on a project to upgrade our ERP software up 2 major versions and it’s a POS with POS support to say the least. I know basic SQL queries and definitely not enough to do anything advanced (definitely enough to be dangerous)….But with PowerShell, I can do things moderately advanced! It may not be pretty, it’s not the best way, but it’s going to save me 45 minutes of monotonous work of deleting old users/user owned schemas when I do the full upgrade in the middle of one of these coming nights.

Note: I am working on all of this in a test environment and will be running some basic queries the day before the update to make sure all my scripts are on the up and up.

[array]$DontDelete = 'public','sysadm','sa','dbo','guest','INFORMATION_SCHEMA','sys','READONLY','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_denydatareader','db_denydatawriter','db_datawriter' $Users = Invoke-Sqlcmd -Query "USE [VMFG] SELECT * FROM sys.database_principals" | Where-Object{$_.name -notin $DontDelete} foreach($User in $Users){ Invoke-Sqlcmd -Query "USE [VMFG] DROP SCHEMA $($User.name)" Invoke-Sqlcmd -Query "USE [VMFG] DROP USER $($User.name)" } 

submitted by /u/BitteringAgent
[link] [comments]

Leave a Reply