Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jgreenhaw
Starting Member
2 Posts |
 Posted - 2013-03-06 : 18:56:22
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-06 : 19:43:51
|
| 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. |
 |
|
|
jgreenhaw
Starting Member
2 Posts |
Posted - 2013-03-06 : 22:36:20
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 00:06:25
|
| 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 themAs 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-03-07 : 00:54:11
|
| 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. |
|
|
|
devguru
Starting Member
1 Post |
Posted - 2013-04-03 : 04:38:33
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 04:41:37
|
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 ServerIts 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Query Builder Tools