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

[usenet]

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.

[usenet]

Hi

"Schuml" wrote:

> 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.
>
Why are you setting a traceflag when exporting to excel? You will need to
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

[usenet]

Hi

"Schuml" wrote:

> 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.
>
Why are you setting a traceflag when exporting to excel? You will need to
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

[usenet]

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

[usenet]

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

[usenet]

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

[usenet]

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

[usenet]

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" wrote in message
news:30C87B1A-A581-42E6-8950-FF9C27D00405@microsoft.com...
> 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
>

[usenet]

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" wrote in message
news:30C87B1A-A581-42E6-8950-FF9C27D00405@microsoft.com...
> 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
>

[usenet]

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