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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » Roles/Permissions for 3rd Party Providers...

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

Rate Topic

Display Mode

Topic Options

Author

Message

gs1975

Posted Wednesday, September 05, 2012 2:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:04 AM
Points: 28, Visits: 573

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

John Mitchell-245523

Posted Wednesday, September 05, 2012 3:00 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,428, Visits: 7,196

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

opc.three

Posted Wednesday, September 05, 2012 8:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1354593

RBarryYoung

Posted Wednesday, September 05, 2012 11:42 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

gs1975

Posted Wednesday, September 05, 2012 2:23 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:04 AM
Points: 28, Visits: 573

Thanks for all your replies. They all make sense.

I will be making my recommendations tomorrow.

George

Post #1354885

« Prev Topic | Next Topic »

Permissions