We recently have upgraded an application from sql2000 to sql2005. After
changing the ODBC on the client machines to point to the new server the users
are now experinacing a "User 'public' does not have permission to run DBCC
TRACEON", and the excel query will not refresh. If we add the user to the
domain admin group the query will run. Obviously this is not an ideal
solution to the problem. What can we do to resolve this issue with out
recreating the spread sheets.

Hi

"Schuml" wrote:

investigate what the trace flag does but at a guess this trace flag will no
longer be needed. To set trace flags you have to be a sysadmin which will
give your users a significant number of privileges more than would be needed.
Therefore if removeing the trace flag is not possible you may want to
consider a different method of creating this export, such as a SSIS package
or Reporting services.

John

Thanks John for the response, we did not have the issue when the data base
was residing on a 2000 SQL Server, it only began when we migrated the
database to a SQL Server, so I guess I am a little confused as to why 2005 is
generating the error. I would like to be able to use the legacy spreadsheet
reports.

There must be some setting in 2005 that is available to allow this

Hi

"Schuml" wrote:

of trace flags and hints should always be reviewed on any service
pack/upgrade as to whether they are necessary or not. The reasons why a trace
flag is being set should also be fully documented. Do you know which flag
this is, where it is being set and why?

John

I Googled for below:
"DBCC TRACEON(208)" 2005

And I found several hits, for instance:
http://forums.microsoft.com/MSDN/Sho...00640&SiteID=1
http://www.fits-consulting.de/blog/P...26f556c10.aspx

It seems it definitely is a behaviour change in SQL Server 2005, regarding permissions to execute
DBCC, and the 208 that Excel uses gets hurt by this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Schuml" <larrys (AT) news (DOT) postalias> wrote

John,

I'd bet that it is DBCC TRACEON(208) which Excel (MS Query?) generates for some reason...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

Hi Tibor

Thanks for that!

John



"Tibor Karaszi" wrote:

Thanks John & Tibor
I saw the same posting when I googled for this error and I tried running the
VB Script described and it did not solve the issue, I have to beleive that
there is a setting or permission that can be assigned to a user. The problem
goes away if the user is a member of the admin group which obviously is not
an Ideal long term solution and it makes me very uncomfortable.

Hi

"Schuml" wrote:

Part of the reply said

1. Go to the ODBC connection and uncheck "Use ANSI qouted identifiers".

Are you using an ODBC connection to do this? Do you have any identifiers
that need enquoting? If you have you may want to change the aliases or define
a view instead!

John

When it crashes you get a SQL server login box up with "Options" Application name will say Microsoft Query....change that to anything else and it will work.

From http://www.developmentnow.com/g/118_...CC-TRACEON.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/