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 |
|
jaksecret
Starting Member
2 Posts |
 Posted - 2010-02-06 : 19:01:50
|
| this isn't as simple as it seems, please read on.I have two clients who want me to store employee information for them in an employees table. each client should not be able to see data for the other client, but I don't want to create one employee table for each client, because if i make a change to schema, i have to do it twice. Though i could just create a view for each client (select * from employees where clientid=1 etc) that would necessitate changing the front end to access view1 for client 1, view2 for client 2, etc. Is there another way, based on LOGIN(or doing something after/during login, like a trigger perhaps, or creating temporary views, to show a user just the data from a table that applies to them, and to have the application on the front end be able to call the data the exact same way? this screams out for a SQL server session variable of some kind.... |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-07 : 02:22:33
|
| You could use the Schema.dbo.Clients - the actual tablejaksecret.Clients - view of only jaksecret's clientsKristen.Clients - My clientsGive permission to the View, and not to the Table, and because of their login if they just do "SELECT * FROM Clients" then will see the data from the view.We do it using Stored Procedures. The client's "Session" has a ClientID, and the SProcs only return data "owned" by the ClientIDIf users are using an application then the SProcs is probably the best way to control the data, if they need to query the tables direct (adhoc reporting etc) then the Views. Probably!" this screams out for a SQL server session variable of some kind..."Give Microsoft a call then ... |
 |
|
|
jaksecret
Starting Member
2 Posts |
Posted - 2010-02-07 : 13:13:57
|
that's a great, great idea. thank you so much. Another idea by a peer of mine would be to have a table listing loginname and some sort of id that could be pulled and appended to every select statement, i.e. select * from employees (where clientid=1)but yours is far cleaner. I admit i didn't understand that schema/views could be used in that fasion. thanks!quote:
Originally posted by Kristen
You could use the Schema.dbo.Clients - the actual tablejaksecret.Clients - view of only jaksecret's clientsKristen.Clients - My clientsGive permission to the View, and not to the Table, and because of their login if they just do "SELECT * FROM Clients" then will see the data from the view.We do it using Stored Procedures. The client's "Session" has a ClientID, and the SProcs only return data "owned" by the ClientIDIf users are using an application then the SProcs is probably the best way to control the data, if they need to query the tables direct (adhoc reporting etc) then the Views. Probably!" this screams out for a SQL server session variable of some kind..."Give Microsoft a call then ...
. |
|
|
|
|
|
|
|
|
Grant access to a subset of data using login?