January 31, 2003 at 3:19 pm
I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.
Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?
I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.
The goal behind this is to set-up replication between my SQL Server (the publisher) and 20 subscribers.
The steps I take are:
1) Connect via 'Client Network Utility'
2) Register the SQL Server group via the 'Register a Server' wizard
3) Here I can see all the databases on the subscribers SQL Server - they would not like me to have access to that. Conversely I fear they will then be able to see all the databases that exist on my SQL Server - although I have given the users only permissions to see one database.
4) Can I get around this and simple set up replication that nothing is seen - either by me in the subscribers databases nor by my subscribers in my database?
Thanks.
January 31, 2003 at 3:54 pm
You can set a default database for each login. They will be able to see the other databases, but not access them, so I'm not sure what your concern is.
If this is asthetic, the app must control this.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 31, 2003 at 4:01 pm
The concern is that our clients would not like me to see the other databases that exist in thier SQL Server - i.e. even if I cannot directly access the database, they would not like me to even see the names of the other databases.
What do you mean by the app must control this?
Thank you very much for your help.
Bianca
January 31, 2003 at 4:05 pm
I agree with Steve on this one but if you want to try something...
One thing you have the option of trying is to revoke the SELECT rights against sysdatabases and EXECUTE rights on sp_helpdb. Make sure the public role still has EXECUTE rights on sp_MSHasDBAccess. I would test this heavily first before implementing. I know it was an issue with Microsoft Access (couldn't create a project, couldn't establish a linked table, etc.).
Even with that said, it's still a kludge... Not sure it's really worth the headache. Is there a real business need to hide the databases?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 31, 2003 at 4:22 pm
Thank you for your reply.
I need to hide the databases so that our clients do not see them - i.e. the name and how many there are.
Also it has been tough to get our clients to accept replication - they will go balistic if they knew we could see the names of their other databases, even though I cannot access them.
It is a pity SQL Server does not allow for this - sigh...this will be a tough one to explain to my managers.
February 2, 2003 at 9:50 pm
>I need to hide the databases so that our >clients do not see them - i.e. the name and >how many there are
I used a Microsoft Professional Support credit trying get this to work - the official answer is "you can't".
February 3, 2003 at 2:48 am
It would seem to me that if it is really that important to hide the other databases, then your client should consider investing in another server and isolating the database that way. The question is how much is it worth for them to do this?
February 3, 2003 at 4:44 am
You could use named instances, run one client per instance.
Andy
February 3, 2003 at 8:18 am
I'm guessing that your databases are named after your client names, right...?
I'll vouch for the "can't do that" answer, and maybe named instances are the answer, but in a situation like yours, an easy answer on this one might be "security by obscurity", meaning you rename your databases "A", "B", "C", etc. If your clients can see a list of database names (that they can't touch), no big deal. They won't -know- who owns database "E" and inquiries about other databases can be delicately redirected to a disconnected helpdesk extension...
Sorry. BOFH flashback.
<big grin>
Cheers,
Ken
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply