SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Query Builder Tools
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jgreenhaw
Starting Member

2 Posts

Posted - 03/06/2013 :  18:56:22  Show Profile  Reply with Quote
I have a strange problem. My company creates a different database for each client we have. So on one SQL box we might have 50 dbs. We are looking to opening up SQL a bit and allowing clients to view the tables and write their own queries. The problem I'm having is with apps like SSMS and others a user will be be able to see all the databases not just theirs. I know I can deny view of databases but that will hide their db too. Anyone know of a query builder tool where we can pass command line options for the server, db, user, pwd that would limit the user to just that connection. Any help would be greatly appreciated.
Thanks,

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 03/06/2013 :  19:43:51  Show Profile  Reply with Quote
I don't know of a good way to do this.

The only option I know of is for a single login(not for a group of logins). For a single login, you need to do two things:

1. DENY VIEW ANY DATABASE TO theLoginName;
2. Make the theLoginNamethe owner of the database(s) that you want him/her to see (not db_owner role - change the ownership of the database using sp_changedbowner system stored procedure or by right-clicking the database name in SSMS and Files tab, owner).

This is not really a good solution - I am not recommending it.

The other possibility you might consider is to let everyone see all the databases, but just obfuscate the name of the databases. A GUID for a name of the database? yuck!

So, in short, I don't know how to do this in a palatable/clean way. Would love to see it if someone has a clean solution.
Go to Top of Page

jgreenhaw
Starting Member

2 Posts

Posted - 03/06/2013 :  22:36:20  Show Profile  Reply with Quote
Thanks James. Kind of hard to believe that MS has made it so difficult to do this. I've tried about 10 3rd party tools also and found nothing that will work for us so far.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/07/2013 :  00:06:25  Show Profile  Reply with Quote
why do you need to open up db for users?
for writing queries etc isnt it better to create a report model which they can utlize to consume fields exposed and do reporting with it. By doing this you create a abstraction layer for users but hide actual dbs from them
As I understand users will be mostly doing data analysis as against doing any transactions (DML operations) so report model should suffice IMO. If your scenario is different, explain us what clients will be using db for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/07/2013 :  00:54:11  Show Profile  Visit russell's Homepage  Reply with Quote
In addition to what Visakh said, I would point out that doing this is a very bad idea. Any single client can affect the performance for ALL clients by consuming too many resources on the server.

Be sure to enlist the usage of Service Broker if you go that route, but know that some queries are NOT governed by service broker.

Finally, be careful of any tricks where the users can see data not intended for their eyes of escalate their privileges.

Truly, a separate reporting environment seems the right solution to me.

Edited by - russell on 03/07/2013 00:54:51
Go to Top of Page

devguru
Starting Member

1 Posts

Posted - 04/03/2013 :  04:38:33  Show Profile  Reply with Quote
How do your users interact with that database?
I'm asking because we have a similar situation in our ASP.NET solution. There is only one DB, actually, but each user can work with different part of that DB and is not allowed to access other parts (tables, views). We use EasyQuery components (http://devtools.korzh.com/easyquery/) to perform this task. They allow you to create a separate data model for each user's role and provide some friendly UI for query building.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/03/2013 :  04:41:37  Show Profile  Reply with Quote
quote:
Originally posted by devguru

How do your users interact with that database?
I'm asking because we have a similar situation in our ASP.NET solution. There is only one DB, actually, but each user can work with different part of that DB and is not allowed to access other parts (tables, views). We use EasyQuery components (http://devtools.korzh.com/easyquery/) to perform this task. They allow you to create a separate data model for each user's role and provide some friendly UI for query building.


thats what you called schema in SQL Server
Its available by default and provided you map them each to different schema they will have only access to tables in their schema without seeing any other users objects

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000