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 |
|
PanzerAttack
Yak Posting Veteran
71 Posts |
 Posted - 2011-08-08 : 07:43:18
|
| Hello, hopefully a simple questions, I just need someone to point to towards what I need to read as I'm suffering from woods for trees.I have created a View which populates a pivot table in Excel. However, I want to restrict what the user sees dependant on who opens the spreadsheet the pivot is in.I'm told I can do this, possibly using the users windows ID but I've never done anything like this, where do I start reading? |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-08-08 : 08:02:01
|
| You can use SYSTEM_USER to show user connected and sp_helpuser to show user membership of Roles. So you could check the user and their Role membership to build custom SQL. But If it was me I would create different Views for the user type and give the users access to the appropriate View. The reason - easier to maintain and is likely to have better performance. |
 |
|
|
PanzerAttack
Yak Posting Veteran
71 Posts |
Posted - 2011-08-08 : 08:05:05
|
| Thanks I shall search on those 2 terms. I was thinking about separate views but I was hoping for 1 front end. An Excel Pivot table. Could I have multiple views and just 1 front end?I'm assuming what this entails is creating some roles i.e. Sales or Finance and then assigning Windows Logon's to those roles? |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-08-08 : 09:53:41
|
| I did a quick search and you can apply permissions to ranges in Excel - so if the different Views are just additional columns it might be simpler to use Excel permissions and have a single View.http://support.microsoft.com/kb/289270I'm sure you could also use VBA in Excel to use different datasources. |
|
|
|
|
|
|
Restrict User Access via Pivot Table