DSN madness

I have an app that accesses a SQL db via a DSN (and MSDE). The user name and
password are contained in the app, and passed to the data source at run
time.
Everything works fine if the user is an Adminstrator (on Windows), but can't
connect if the user is a User or Power User. If I promote the user to
Administrator, it works; if I demote him back again, it doesn't.
The really strange thing is, this used to work just fine!
This is driving me nuts. Anyone have a solution?Paul Pedersen (nospam@.no.spam) writes:
> I have an app that accesses a SQL db via a DSN (and MSDE). The user name
> and password are contained in the app, and passed to the data source at
> run time.
> Everything works fine if the user is an Adminstrator (on Windows), but
> can't connect if the user is a User or Power User. If I promote the user
> to Administrator, it works; if I demote him back again, it doesn't.
> The really strange thing is, this used to work just fine!
> This is driving me nuts. Anyone have a solution?
And the error message is?
From what you say it sounds like a permission problem on the DSN.
(Disclaimed: I never liked or understood DSN. I prefer to live in a
DSN-less world.)
By the way... I don't know what sort of app this is, but embedding
username/password into the app, does not sound like something I would
do. Would it not be better to use Windows authentication?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9798CEF77432EYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> And the error message is?
Something on the order of "Database does not exist or access denied".
Like I said, this used to work fine. After a few ws, it began to get
slow. Then it began to have occasional errors signing in. Now it won't sign
in at all. Bizarre.
I have reinstalled and re-configured MSDE several times. Recreated the DSN
too. Works fine for Adminstrators, doesn't work at all for others. Perhaps I
made a mistake in configuration, but I can't think what it might be.

> From what you say it sounds like a permission problem on the DSN.
> (Disclaimed: I never liked or understood DSN. I prefer to live in a
> DSN-less world.)
> By the way... I don't know what sort of app this is, but embedding
> username/password into the app, does not sound like something I would
> do. Would it not be better to use Windows authentication?
The user name that is passed is assigned a specific role in the database.
Although there's only one instance of the app at present, eventually it
could be run by a number of users from a number of different machines. I
prefer not to have to track all those users, who have no other business in
the database anyway. This way, I can just track the app. Wherever it's
running from, it can sign in and get its data.
At present, the database is local to the machine the app is running on.
Within a couple ws, the database will be moved to a server.|||Paul Pedersen (nospam@.no.spam) writes:
> Something on the order of "Database does not exist or access denied".
> Like I said, this used to work fine. After a few ws, it began to get
> slow. Then it began to have occasional errors signing in. Now it won't
> sign in at all. Bizarre.
Since it goes slower and slower, it sounds like a networking problem.
What is the contents of the DSN?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9799687B2B9DYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> Since it goes slower and slower, it sounds like a networking problem.
I don't see what that might be. 1, It's not currently running on a network.
It's all local. 2, It works fine for Adminstrators, or even the account in
question if I promote it to Administrator (which I have done, just to get it
running - it's mission critical - but of course I don't want to leave it
that way). 3, Not everything was getting slow, just first getting access to
the data. Queries ran well enough.

> What is the contents of the DSN?
Nothing special. Created in the ODBC Control Panel as a System DSN. Set as
SQL Server authentication (user name & password) rather than Windows login.
User name and password are not saved (they are provided by the app). Default
database setting is ignored, because that's specified in the db for the app
user. Basically it has nothing but a name, which the app looks for, a
specification of the SQL Server driver, and the instance name of MSDE (I use
a named instance).
At present, there is no other access to that MSDE instance, which is the
only SQL Server instance on the machine.
It works fine, for Administrators. And it used to work for Limited Users,
too.|||Hey Paul,
It could be something obvious, like you set up a User DSN instead of a
System DSN; in that case, only the original user and/or the
Administrator group would have access to that DSN entry. Or, it could
be that the accounts don't have access to the Windows registry entry
required to load the System DSN information.
Take a look at :http://support.microsoft.com/kb/306345/EN-US
and see if it helps.
Stu|||Paul Pedersen (nospam@.no.spam) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9799687B2B9DYazorman@.127.0.0.1...
> I don't see what that might be. 1, It's not currently running on a
> network. It's all local. 2, It works fine for Adminstrators, or even the
> account in question if I promote it to Administrator (which I have done,
> just to get it running - it's mission critical - but of course I don't
> want to leave it that way). 3, Not everything was getting slow, just
> first getting access to the data. Queries ran well enough.
I've seen issues like this on a local machine. It that case I was logged
as admin, the problem is that shared memory freaked out.
I guess that this machine is in a domain, and not a solitary workstation?
Then there may be contacts with the domain controller. (Windows networking
is nothing I know well, so exactly what problems that may be, I don't know.)

> Nothing special. Created in the ODBC Control Panel as a System DSN. Set
> as SQL Server authentication (user name & password) rather than Windows
> login. User name and password are not saved (they are provided by the
> app). Default database setting is ignored, because that's specified in
> the db for the app user. Basically it has nothing but a name, which the
> app looks for, a specification of the SQL Server driver, and the
> instance name of MSDE (I use a named instance).
I had hoper that you have posted it as is.
What I wanted to know is whether you specify any network library.
In any case, verify in the Client Network Utiility that shared memory
is enabled. Although I mentioned that I've had problems with shared
memory, shared memory is what works best on a local computer.
Oh, since this is an MSDE machine, the Client Network Utility is probably
not available from the start menu, but running "CLICONFG" from command-
line should bring you to the version that comes with the MDAC.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1143942800.875293.89510@.j33g2000cwa.googlegroups.com...
> Hey Paul,
> It could be something obvious, like you set up a User DSN instead of a
> System DSN; in that case, only the original user and/or the
> Administrator group would have access to that DSN entry.
No, it was a system DSN. But thanks for trying.

> Or, it could
> be that the accounts don't have access to the Windows registry entry
> required to load the System DSN information.

> Take a look at :http://support.microsoft.com/kb/306345/EN-US
Hmm, that sounds interesting. I don't understand how that could have
happened, because it used to work.
I'll take a look at it.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97998597D1CA0Yazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> I've seen issues like this on a local machine. It that case I was logged
> as admin, the problem is that shared memory freaked out.
> I guess that this machine is in a domain, and not a solitary workstation?
> Then there may be contacts with the domain controller. (Windows networking
> is nothing I know well, so exactly what problems that may be, I don't
> know.)
It was in a domain, but the server's motherboard died, so I moved the
database from MSDE on the server to the local machine, and set up MSDE on
it, and have been running it all locally. That machine no longer has any
network connection; it's completely standalone now. Like I said, it's been
working fine for a while.
I should have a new server up within a couple ws (it's a volunteer job,
so it gets lower priority), this time with actual SQL Server instead of
MSDE, so maybe I can get it working again since I'll have the Enterprise
Manager to play with.

>
> I had hoper that you have posted it as is.
How would I do that? As far as I know, the ODBC control panel has control
over that. I don't even know where the control panel stores its DSNs - I
just assumed in the registry somewhere. It's a system DSN, not a file DSN.

> What I wanted to know is whether you specify any network library.
There's nothing unusual in the way I set it up. I just chose SQL Server
driver, gave it the expected name, typed in the instance name of the server
(for some reason, it doesn't show up in the list), set it for SQL Server
authentication, and left everything else at the default.

> In any case, verify in the Client Network Utiility that shared memory
> is enabled. Although I mentioned that I've had problems with shared
> memory, shared memory is what works best on a local computer.
> Oh, since this is an MSDE machine, the Client Network Utility is probably
> not available from the start menu, but running "CLICONFG" from command-
> line should bring you to the version that comes with the MDAC.
Thanks. I would never have found it without that tip.
OK, I'll check that.
Thanks for all your help.sql

Dropping article

Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis - hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.
|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
sql

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script. Do
es any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Drop login error

Hi
I am trying to drop a windows login via Management Studio (CTP June) but it comes up with
'Login domain\user' has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I cannot see of any permissions that this user has granted. Is there a system view in SQL 2005 which shows permissions this user has granted?
Thanks,
Priyanga

You need to look at the "Security Catalog Views" topic in Books Online. Specifically, take a look at sys.server_permissions and sys.server_principals views to map principal_id of the login you are looking at to the permissions. You can do that at database level as well.

Something along these lines (use this as an idea):

select * from sys.server_permissions
where grantee_principal_id =
(select principal_id from sys.server_principals where name = N'DOMAIN\user')

Alternatively, in Object explorer, you can select Security->Logins->DOMAIN/user, Properties of the login, go Securables and add objects (e.g. server(s)) and this will show you explicit permissions of this login on the object(s). You can revoke there explicit permissions as well.

Then you can also go to the properties of the server in Object Explorer, Permissions and click on the login and click on Effective Permissions. This will show all the permissions for this user based on its role membership and explicitly granted permissions.

HTH,
Boris.

|||

Thanks Boris.

What was causing the issue was that the principal_id (nation\pk0159_a) was linked to grantor_principal_id in the sys.server_permissions as apposed to the grantee_principal_id. The object responsible for this issue was an endpoint. When i dropped the endpoint, i was allowed to drop nation\pk0159_a.

This explains why no objects were showing as being granted permissions by nation\pk0159_a in object explorer in SQL Management Studio.

Cheers,
Priyanga

Driver to connect to Informix via DTS?

Hi, folks!

I want to pump data from Informix to SQL Server using DTS to fill / fresh up my SQL-Server data warehouse every day.

In the SQL documentation I found "Don't use the ODBC driver from Informix"

Which is a good choice of ODBC/OLE-DB drivers to deal with Informix via SQL servers DTS?

Regards from Berlin!

SeBaFluDownload Informix driver from IBM website and set it up on your machine to create a DTS package. I have used it to create a DTS package and run it for over a year now.|||What was the error with current driver?
That was a general recommendation, but to followup the data import/export as referred comaptible driver must be used.|||Hi, Joe!

Originally posted by joejcheng
Download Informix driver from IBM website and set it up on your machine to create a DTS package. I have used it to create a DTS package and run it for over a year now.

OK, let's try it <s>...

... but may be I'm stupid - but I can't find any ODBC driver at the IBM site - can You advice me to the related URL?

Many thanx from Berlin!

SeBaFlu

Drillthrough Functionality via Excel Add-in

Hi,

I'm using the Analysis Services Excel Add-In to query a Cube built with partitions.

When using the drilldown functionality for my current\default partition the function seems to work fine.

However, when I try to drilldown on data in the other partitions, the following error message appears:

"Unable to Drillthrough on this Cell"

Would someone please be able to shed some light on this?

Many Thanks

Jonathan Derbyshire

Jonathan_Derbyshire@.Hotmail.com

We have the same problem (also using OWC controls). do you have the solution?
if yes, please, let me know.
Thanks

Carlos
tfcarlos@.hotmail.com
|||No I'm still stuck.

If anyone can help I would be very grateful...

Thanks

Jon Derbyshire|||I think Excell can't drillthrough over partitiones.
I'm not sure but if you have more than 1 partition you can't drillthrough. Did you try to execute the drillthrough query against an ado connection?
It's a large cube?
Do you have to show many columns in drillthrough?
Because you can make some calculated measures to solve this (be carefull with the result).|||Why do you think that Excel cant drillthrough over partitions? Have you read this somewehere or are you just speculating?

I can drillthrough - but only on the current/default partition...

Its a Medium to large cube.

Drillthrough shows about 20 columns, but I've tried limiting it to 5 and still the same error.|||I'm speculating.
But I found the solution:
In addition to enabling drillthrough at the cube level, you can also modify the options on each individual partition in cubes that contain more than one partition. You can access a dialog box analogous to the one in Figure B through the Partition Wizard—to access the Partition Wizard, right-click the partition and select Edit from the context menu—by clicking the Advanced Settings button and Drillthrough Options. The resulting Partition Drillthrough Options dialog box lets you change the columns and filter to use when creating drillthrough data from that partition. The ability to set drillthrough options on individual partitions means that if you execute a drillthrough operation on a cell that aggregates data from more than one partition, each partition returns its own result set and the columns in those result sets can differ from one another. Application developers need to be aware of this behavior so that they can display the results accordingly. The administrator's next task, configuring drillthrough security, requires that you use the Cube Role Manager dialog box and click the Drillthrough setting for the role you want to grant access to. Each role has a security setting, called Allow Drillthrough, that you can select to let that role execute the queries that perform a drillthrough operation. After you enable drillthrough and configure security, you can implement drillthrough for applications.
Text From Implementing Drillthrough for Developers in http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=41679|||Thanks - I've been through and it appears that drillthrough was already definitely enabled...

It still doesnt enable me to drillthrough as I wish. I've posted my question on the site you provided in an attempt for more help.|||I want to ask you.
Are you using sql 2000 or sql 2005?
If you are using sql 2005, are you connecting from excel in the server or in a remote computer?
If you are connecting from a remote computer, what have you installed in the computer to making it works?
I can't connect from a remote computer with excel to AS 2005.
Thanks in advance|||2000 version

Drilldown

Is there a way to disable a level drill down? I want to keep the level set to All and not have the user to drill down via the report.

One possibility is to try custom dimension security:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

After a Microsoft SQL Server 2005 Analysis Services (SSAS) database role has read or read/write permission to a dimension, you can define security on each dimension member (also called dimension security). By default, a database role is not allowed access to any members of any dimension. You have to grant permission to allow access to specific attributes and attribute members within the dimension. The properties that are available for viewing attributes and attribute members are: IsAllowed, AllowedSet, DeniedSet, ApplyDenied, VisualTotals, and DefaultMember. The type of access that members of a dimension role have is based on the dimension access granted, either read or read/write.

>>

|||

I'm following the BOL, but I don't see the "Dimension Custom Data Access" option in the "Select a page pane" when editing the Analysis Service database role. Is this only available in a specific version/edition of SSAS? I'm running Developers edition w/ SP1 installed.

Thanks.

Drilldown

Is there a way to disable a level drill down? I want to keep the level set to All and not have the user to drill down via the report.

One possibility is to try custom dimension security:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

After a Microsoft SQL Server 2005 Analysis Services (SSAS) database role has read or read/write permission to a dimension, you can define security on each dimension member (also called dimension security). By default, a database role is not allowed access to any members of any dimension. You have to grant permission to allow access to specific attributes and attribute members within the dimension. The properties that are available for viewing attributes and attribute members are: IsAllowed, AllowedSet, DeniedSet, ApplyDenied, VisualTotals, and DefaultMember. The type of access that members of a dimension role have is based on the dimension access granted, either read or read/write.

>>

|||

I'm following the BOL, but I don't see the "Dimension Custom Data Access" option in the "Select a page pane" when editing the Analysis Service database role. Is this only available in a specific version/edition of SSAS? I'm running Developers edition w/ SP1 installed.

Thanks.