I've avoided having multiple instances in the past based on two, possibly flawed!, assumptions:
- Resources allocated separately to each instant, rather than shared (Memory principally, also TEMPDB)
- Management of the whole system becomes split, rather than all-in-one-pot
I suppose I could partly solve the Management issue by having, for example, the MDF, LDF and Backups from both/all instances using the same folders. I'd have to avoid databases of the same name in each instance (but that would drive me insane anyway!)
[Master] etc. are still going to use separate folders (for MDF/LDF)
We now have a situation where I have a Vendor that has Admin rights on the (single shared) SQL box and we need to mount some databases that they should not be allowed to see (including not fiddling/viewing SQL Agent jobs associated with them etc., so just hiding the DBs would not be sufficient)
We also perceive that we might need to prevent the current Admin Users we have (as distinct from IT folk and Database DBAs - who are trusted with acess to all company confidential information) from viewing some forthcoming financial and HR databases
So I'm thinking of a second instance for "restricted databases" ... but really I have no idea what criteria you Big Iron folk use to decide which databases to put in which Instances, and what your view is on any physical etc. resources that you then have to apportion between the Instances.
I could also do with education, please, on any issues / CONs that arise - do you think that management of multiple instances is more hassle than a single instance for example??
I'm also assuming that you will, without doubt!, put me straight if my understanding of Instances (e.g. allocation of physical resources) is flawed 
I'd put the Vendor stuff in its own instance. If it needs access to corporate data, set up a linked server to the corporate instance and restrict their access (double abstraction + least privilege). Also, try to remove sysadmin from the Vendor. I just can't believe they really need it. Find out what really is needed and grant those privs.
For the second part, create AD groups and custom database roles, Say "corp\restricted" for the group and "restricted access" for the db role. Add the current Admin uses to the new group and in the custom db role, deny access to the restricted tables. Then you manage only the AD group and the DB role to change who has access to what and at what level.
Yeah, they already don't have SYSADMIN ... but they need "SQLAgentOperatorRole" which means they can see all JOBs - and some vendors stuff miles of code, rather than just an innocuous SProc call, in them.
Also, just names of DBs - even if they can't open them - might be seen as an unnecessary security risk
But my greatest worry is that I doubt that we would safely maintain all Permissions / Rights so that there were, not now and not ever!!, any loophole that allowed them unnecessarily elevated permissions - even if we explicitly DENY some rights.
Well, if you use double abstraction and the vendor has no permission to change database roles or AD group membership, it would take a significant bug in either AD or SQL Server to allow permission elevation.
1 Like
I think unlikely that we would accidentally give them permission to elevate themselves, I think much more likely that we would fail to block "something somewhere".
Our IT folk never worry about this issue when they are building conplex User Permissions Edifices, and clearly they sleep easily at night! ... but it has always seems to me to be an accident waiting to happen
I haven't read the whole thread or even the long first post, so sorry if this has been covered.
I do not use or recommend multiple instances. My view is one instance per server. If we need another instance, we deploy a new virtual machine. If it were a physical server and non-production, then I would consider a new instance.
Thanks Tara, since creating the thread I've read similar thoughts from a number of people around the web ...
Not in a position (i.e. Budget/Cost) to split our SQL Hardware into two at present, and what we have mostly runs at very low percentage, so its just a security issue for us.
Perhaps I should put the effort into the making sure that Vendors have "least privilege" as gbritton suggested, and leave it at that. I just don't trust myself / colleagues to do that with 100% guarantee