I’ve added a new parameter to my permissions scripts (well, just the main two for now). Fair warning, it’s in Beta so if you notice any issues please let me know.
I’d been getting a lot of requests to Copy the permissions from service account ABC to service account DEF recently. This involved scripting out the permissions for service account ABC across multiple databases on multiple servers and then changing the name of the service account in the script to DEF. It’s not hugely difficult but on at least a couple of occasions I forgot to do the replace step which kind of defeated the purpose and involved a lot of double checking on my part. So in order to speed the process up and remove at least one manual step I added the @CopyTo parameter. So now instead of:
EXEC sp_DBPermissions 'All','ABC', @output = 'CreateOnly';
-- Copy script and change ABC to DEF.
-- Run modified script.
I only have to do
EXEC sp_DBPermissions 'All','ABC', @CopyTo = 'DEF', @output = 'CreateOnly';
-- Copy and run script.
This sped things up more than you would think. One warning though. I’m literally just constructing the string with the @CopyTo value. I’m not doing any validation (yet). So if you are using sp_DBPermissions the login will need to exist, and if you are using sp_SrvPermissions I’m not checking to see if the AD User/Group exists, and you’ll run into problems copying permissions from a SQL Server Id to an AD/Windows Id or vice versa. Hopefully I’ll get all of that worked in in the near future.