Data Display in Reports based on windows Authentication ID

Hi Experts,

I have a reporting scenario, where the reports are fetched from Analysis Services.

The reports should display data only spcecific to that user.

All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.

Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated

Thanks,

Hi,

Easier said than done. I guess that the easier path would be to use data filtering based on parameters values limited by userid.

It depends, if you access cubes directly as well, i.e. from Excel, then that would be different. You would have to create custom MDX dimension access formulas based on roles and map these roles to Windows groups.

Assuming you are only going after AS data from RS, then you would still need to create an access policy.

i.e. you can segregate access to data slices based on membership to Windows groups.

We have found that using cumulative membership to groups was the most flexible and did not require too many groups.

You have to find a way to load LDAP (or Windows AD groups and membership) data, then each time an user access the report, you use the userid info to check what is the acceptable list of values for your parameters.

This way, a given user will be able to see and select only parameters values acceptable for her job function.

That is quite a lot of code, functions and procedures to write, no quick fix.

First thing first, you need the AD or LDAP data, there are quite a few ways to get it. I remember that SQL Server magazine ran an article a few month ago about getting it with a SSIS package.

You can also settle for another easier way, like calling Exporter Pro from a Windows scheduled task. Exporter Pro is part of the Hyena software.

Your question is not the type of question where a quick and precise answer is available. I can only give some high level clues. The rest would take time and require insider knowledge of your situation and requirements.

Hope it helps somehow

Philippe

Damaged database

I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?If that didn't work then you're in a bit of trouble but you can try this
**Make sure you have a copy of your MDF first and a backup of master**
Replace the filenames with your filename !!!
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the log file
will be small so it will be worth increasing its size
Unfortunately your files will be called fake_Data.MDF and
fake_Log.LDF but you can get round this by detaching the
database properly and then renaming the files and reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ATS967" <anonymous@.discussions.microsoft.com> wrote in message
news:098d01c3a06f$83c18e20$a301280a@.phx.gbl...
I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?