New parameter in sp_DBPermissions and sp_SrvPermissions: @CopyTo

Leave a comment

August 18, 2022 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: