User 'public' does not have permission to run DBCC TRACEON

[Database Administrator]

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" wrote in message
news:C6AB05B9-D5D4-4080-A6BB-DAD289333438@microsoft.com...
> Hi
>
> "Schuml" wrote:
>
>> 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
>>
> Trace flags are not something you should set without due consideration, use
> 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

[Database Administrator]

Hi Tibor

Thanks for that!

John



"Tibor Karaszi" 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" wrote in message
> news:C6AB05B9-D5D4-4080-A6BB-DAD289333438@microsoft.com...
> > Hi
> >
> > "Schuml" wrote:
> >
> >> 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
> >>
> > Trace flags are not something you should set without due consideration, use
> > 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
>
>

[Database Administrator]

Hi Tibor

Thanks for that!

John



"Tibor Karaszi" 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" wrote in message
> news:C6AB05B9-D5D4-4080-A6BB-DAD289333438@microsoft.com...
> > Hi
> >
> > "Schuml" wrote:
> >
> >> 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
> >>
> > Trace flags are not something you should set without due consideration, use
> > 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
>
>

[Database Administrator]

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.

[Database Administrator]

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.

[Database Administrator]

Hi

"Schuml" 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.

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

[Database Administrator]

Hi

"Schuml" 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.

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

[Bruce Wray]

This post is a bit old, but to record a "how to fix", as I'll likely Google and find it again:

This problem is related to the upgrade to SQL 2005 and the changes to 'public' permissions, in that they cannot run DBCC TRACEON(208) to enable Quoted Identifiers.

A simple way to fix the problem in an Excel document:
- for each of the affected queries, right click and select "Refresh Query"
- this will cause a series of error messages ('public', 'us_english', 'db_name', etc)
- next, it will bring up the ODBC selection pop-up
- on the ODBC selection pop-up, pick the correct connection and click OK
- ** IMPORTANT ** on the next screen, expand the "Advanced Options >>" (or just "Options >>") and remove "Microsoft @Query" from the Application Name.
- hit okay and then "Refresh" the query again.

When connecting via ODBC from an Excel file to a SQL 2005 server, you must remove the Application Name of "Microsoft @Query" for it to function.