SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Roles/Permissions for 3rd Party Providers doing Rollouts/updates


Roles/Permissions for 3rd Party Providers doing Rollouts/updates

Author
Message
gs1975
gs1975
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 868
Hi,

I have just started at a new company and I am tightening security at the moment.

One of our 3rd party providers has a login to perform software rollouts/upgrades on one of our servers.
They previously had sysadmin level of access (which I have now reduced).

Is there a best practice for what roles and permissions a 3rd party provider should have for performing upgrades and data rollouts on a server, or does it depend on a number of factors which I need to continue investigating?

Thanks,
George
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32671 Visits: 16630
George

I think it depends. The ideal solution is for them to develop the solution on their own systems (possibly a copy of your database) and provide you with a script to run on your own system. If they can't, or won't do that, then you probably need to quiz them thoroughly about the changes they're going to make and give them only the access they need to make them, and only for the duration of the change.

John
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36716 Visits: 14411
I recommend running in FULL recovery and taking lots of backups. If the rollout is multi-phased or prolonged then take a FULL backup at each logical stopping point. You can always restore these backups to other instances and do before and after compares to make sure what they say they did and what they actually did match up. Being in FULL recovery and having log backups also allows you to recover to a point in time if needed.

If they legitimately require sysadmin privs then I would grant them to a specific login that belongs only to them for purposes of the rollout, then take away sysadmin privs when the rollout is done. Consider setting up an Extended Events Session (or Trace) to capture the activity associated with their login while they're doing the rollout in case you need to refer to it just in case should something result in a problem after the rollout.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32984 Visits: 9518
And make them do their rollout/upgrade changes on a test or backup copy of your database first. Only when that suceeds should you consider giving them (temporary) access to your production database.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
gs1975
gs1975
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 868
Thanks for all your replies. They all make sense.

I will be making my recommendations tomorrow.

George
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search