November 10, 2009 at 2:41 am
Hello.
Is it possible to create a user on SQL 2008 Express (possible Standard too, but I guess that doesn't make difference here) that would have the following restrictions?:
- able to connect to SQL server
- only able to SEE and ACCESS only selected databases (the see-view part is critical)
- able to insert, update, delete in the selected dbs
I think all those points are trivial except the see-view part. The reasoning is that we need to give access to selected DBs to an external user, but he/she cannot be allowed to "browse" the SQL server freely and not even know of existence of other DBs.
Is this possible to set up? If so, what roles/permissions should I give that user?
Thanks in advance
November 10, 2009 at 7:17 am
If the person is using SSMS to connect to the server there is no way to keep them from seeing databases that exist (at least not that I know of) on the server. They can't access the databases, but they can see them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 7:44 am
That's what I was afraid of, but thanks anyway for confirming.
Then I also guess there's no way to create a user on the SQL server without him being member of the Public role?
You also write that if the user can use SSMS, then he can see the DBs. The same would apply for command line tools, right? Issuing simple "SELECT * FROM sysdatabases" will tell the user same info, won't it?
November 16, 2009 at 3:43 am
i face the same problem can anybody help us on that
November 16, 2009 at 10:52 pm
I am wondering why them seeing the database is such a deal breaker, they can't access it..
You might look at:
http://msdn.microsoft.com/en-us/library/ms189077.aspx
If you run this command:
REVOKE VIEW ANY DATABASE FROM public
all they will see is master and tempdb..
I would be careful with this..
CEWII
November 19, 2009 at 12:38 am
Thanks Elliott, I'll try out your suggestion.
It's a big deal, because the database names can contain VAT ID numbers of companies or their names and since multiple clients "coexist" on the same SQL, they can't even see other companies' databases. It's a business issue, not technical.
November 19, 2009 at 6:30 am
Thanks Elliott, works like a charm.
November 19, 2009 at 7:23 am
Rambler (11/19/2009)
Thanks Elliott, I'll try out your suggestion.It's a big deal, because the database names can contain VAT ID numbers of companies or their names and since multiple clients "coexist" on the same SQL, they can't even see other companies' databases. It's a business issue, not technical.
I feel obligated to say that including restricted information like VAT ID numbers in the database name is almost certainly a bad practice.. I would caution against this..
CEWII
November 19, 2009 at 11:10 am
I agree, but it's the application (3rd party) that creates the DB names like this, so there's little control over it.
November 19, 2009 at 12:30 pm
Rambler (11/19/2009)
I agree, but it's the application (3rd party) that creates the DB names like this, so there's little control over it.
I completely understand the point but I would go a step further and say that the vendor should be notified that this practice is bad and may violate the EU privacy rules. That EU part is a bit of a shot in the dark but from what I've read it is entirely possible.. I made a bit of an assumption about where you were based on the VAT stuff.
Even third-party applications that use bad practices should be called to task..
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply