Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Roles/Permissions for 3rd Party Providers doing Rollouts/updates Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 2:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
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
Post #1354380
Posted Wednesday, September 5, 2012 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 5,438, Visits: 10,140
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
Post #1354388
Posted Wednesday, September 5, 2012 8:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 7,135, Visits: 12,748
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
Post #1354593
Posted Wednesday, September 5, 2012 11:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #1354788
Posted Wednesday, September 5, 2012 2:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
Thanks for all your replies. They all make sense.

I will be making my recommendations tomorrow.

George
Post #1354885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse