non-sa users get access denied in OLE DB query

On SQL Server 2005, 64 bit, this query:
SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
MISDB2A.FASMSCAC_STD_CARRIER_CODE')
Gets this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
I works if it is run by the sa login, or by a login that has been granted
the sysadmin Server Role.
How can I permission non-sa logins to access the OLE DB provider?Ad-hoc remote queries are disabled by default in SQL 2005. Check the
"Surface Area Configuration for Features (Ad Hoc Remote Queries) - Database
Engine " topic in BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/fe754945-2cd2-4cc1-8ae1
-4de01907b382.htm).
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:0896B540-3262-42A8-8D4E-97D900C35A91@.microsoft.com...
> On SQL Server 2005, 64 bit, this query:
> SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
> SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
> MISDB2A.FASMSCAC_STD_CARRIER_CODE')
> Gets this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access
> this provider through a linked server.
> I works if it is run by the sa login, or by a login that has been granted
> the sysadmin Server Role.
> How can I permission non-sa logins to access the OLE DB provider?|||It has been turned on!
With it left off, EVERYBODY gets this mssage:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
So, with ad hoc turned on, how do you permission non-sa logins to access the
OLE DB provider?|||Stu (Stu@.discussions.microsoft.com) writes:
> But turned on, uness you have sysadmin privs, you get this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access this provider through a linked server.
> So, with ad hoc turned on, how do you permission non-sa logins to access
> the OLE DB provider?
Books Online gives us this little tidbit:
OPENROWSET can be used to access remote data from OLE DB data sources
only when the DisallowAdhocAccess registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.
So that seems to be the key.
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|||> i search the registry for DisallowAdhocAccess and I found that under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL.1\Providers
> there is a whole list of providers. Of these SQLNLCI has
> DisallowAdhocAccess
> set to 0, but the others not. I did a test with a plain-test user, and
> it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
> did not have DisallowAdhocAccess listed.
Yes, you nailed it. In BOL, it also stands "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option." In addition, there is a funny thing: you can set this
option to 1 through SSMS (Server Objects - Linked Servers - Providers -
properties of a specific provider). When you check the checkbox, SSMS
creates the registry key. However, you cannot set it to 0 with SSMS. When
you uncheck the checkbox, SSMS deletes the key. So the only option to set it
to 0 is with registry editor (ok, or with SMO, I guess). Not very
recommendable.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Yes, you nailed it. In BOL, it also stands "This option controls the
> ability of non-administrators to run ad hoc queries. Administrators are
> not affected by this option." In addition, there is a funny thing: you
> can set this option to 1 through SSMS (Server Objects - Linked Servers -
> Providers - properties of a specific provider). When you check the
> checkbox, SSMS creates the registry key. However, you cannot set it to 0
> with SSMS. When you uncheck the checkbox, SSMS deletes the key. So the
> only option to set it to 0 is with registry editor (ok, or with SMO, I
> guess).
Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
In my opinion this is a bug. Someone has apparently not understood how
this provider option works. I've submitted
https://connect.microsoft.com/SQLSe...=2574
94
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|||I'm working now.
Thanks guys, for your research and feedback!|||Voted:-)
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98D4B72B433ADYazorman@.127.0.0.1...
> Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
> In my opinion this is a bug. Someone has apparently not understood how
> this provider option works. I've submitted
> https://connect.microsoft.com/SQLSe...=25
7494
>
> --
> 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

nonoandy

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andy

nonoandy

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andy

nonoandy

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andy

nonoandy

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andy

Non-admin Citrix users cannot connect to SQL Server

We have an application that accesses a SQL 2000 server that is running on
several Citrix servers. Recently users started getting a message from the
application that there was a catastrophic failure when connecting to SQL
server. This was only happening if the user was not an administrator on the
Citrix server.
I tried connecting to SQL server from Enterprise Manager and Query Analyzer.
If I was logged on as an admin it worked, but if not, it did not work. In
Query Analyzer if I tried to log on to SQL server using a bad password, I got
back a message about bad user or password. When I put in the correct
password (or if using integrated security) the screen briefly acts like it is
connecting but then the entire isqlw program shuts down. Enterprise manager
shuts down when I try to connect to a server. As a non-admin user I am able
to connect using ISQL.exe.
We've checked on rights to the SQL files and everything looks OK. Is there
anything else to check?
Thanks
LehrSJ
Hello there,
I would like to suggest you applied the SQL 2000 SP4 first.
And please let me know the error log in the SQL Server.
This issue may related with some permission of folder. If you use the SQL
Authentication to connect the SQL server, did this issue occur?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Non-Admi users accessing jobs in Enterpise Manager

Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
Igor
Yes, but you'd have to code your own stored procedure and it would not be
called from SQL EM. You'd have to let them just execute it from Query
Analyzer or some other client-side tool.
You could always build your own Admin Interface using SQL-DMO.
Sincerely,
Anthony Thomas

"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:uVvyHrAAFHA.2316@.TK2MSFTNGP15.phx.gbl...
Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
Igor

Non-Admi users accessing jobs in Enterpise Manager

Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorYes, but you'd have to code your own stored procedure and it would not be
called from SQL EM. You'd have to let them just execute it from Query
Analyzer or some other client-side tool.
You could always build your own Admin Interface using SQL-DMO.
Sincerely,
Anthony Thomas
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:uVvyHrAAFHA.2316@.TK2MSFTNGP15.phx.gbl...
Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
Igor

Non-Admi users accessing jobs in Enterpise Manager

Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorYes, but you'd have to code your own stored procedure and it would not be
called from SQL EM. You'd have to let them just execute it from Query
Analyzer or some other client-side tool.
You could always build your own Admin Interface using SQL-DMO.
Sincerely,
Anthony Thomas
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:uVvyHrAAFHA.2316@.TK2MSFTNGP15.phx.gbl...
Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
Igor

NOLOCK hint on views?

Hi all

If i have a view:

CREATE VIEW vw_Users

AS

SELECT * FROM Users WITH(NOLOCK)

Is it suggested to use nolock in views?

And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?

CREATE PROC [dbo] .[usp_GetCompanyUsers]

AS

SELECT * FROM Companies WITH(NOLOCK) JOIN

vw_Users WITH(NOLOCK) --<< --is this suggested?

If you using NOLOCK hint while creating view, you don't need to use the NOLOCK again when accessing the view. The locking is always done on the table and not on the view.

Noise Words

We were getting errors when users searched for the term "r&d" - with or
without quotes - so we removed all single-letter words from the noise
word files (.eng, .enu and .dat). We then started Microsoft Search
again and did a full population - "rebuild" was grayed out.
This stopped the error but now we get no results.
Questions:
1) Why are we getting no results?
2) Is "&" a word breaker? Can we change this?
Are there any messages in the event log from MSSearch or MSSCI?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<s.cawood@.gmail.com> wrote in message
news:1143716799.014202.246690@.i39g2000cwa.googlegr oups.com...
> We were getting errors when users searched for the term "r&d" - with or
> without quotes - so we removed all single-letter words from the noise
> word files (.eng, .enu and .dat). We then started Microsoft Search
> again and did a full population - "rebuild" was grayed out.
> This stopped the error but now we get no results.
> Questions:
> 1) Why are we getting no results?
> 2) Is "&" a word breaker? Can we change this?
>

No. of User Accounts in SQL SERVER 2000 and SQL SERVER 2005 ..plz

I want to know the maximum number of users that can be created in SQL SERVER 2005 and the previous version...?

Yorker:

I am going to assume that you will hit the limitations of the "uid" field. This field is a SMALLINT datatype; however, it appears that groups begin @. 16384 and that this field must be non-negative. Therefore, it appears that a user database MIGHT be limited to 16384 distinct users. However, on a serverwide database you can have many databases therefore leading to at least N x 16384 distinct users. There does not look to be any "variable size" limitation on the LOGIN side because this is based now on SIDs whereas in much older versions of sql server it was formerly based on "SUID"s -- The cardinality of the number of SIDs appears to be enormous.

Would someone please check me on this?

Dave

|||The maximum number of logins in 2000 was 35,365, in 2005 it was increased to 65,000 (I think).

However, I would HIGHLY recommend using windows authentication and group membership for rights, instead of 35,000+ SQL logins.|||any evidence to prove your numbers... ? Gentleman ?|||See this: http://msdn2.microsoft.com/en-us/library/ms187376.aspx

This says in 2005 "you can create more than 32,767 users". I was slightly off with my number in 2000. I thought I read somewhere else about the 64k limit in 2005, but I might be mistaken.

I still it is a nighmare to manage 32k logins.|||From http://msdn2.microsoft.com/en-us/library/ms187376.aspx, it infers that SQL2K can only have 32767 SQL users, but SQL2K5 can have more. How much more, I don't know off-hand.

But this doesn't say how many logins there could be (although I would guess it would be the same). http://msdn2.microsoft.com/en-us/library/ms174355.aspx doesn't give many clues.

I would strongly recommend using Active Directory, where the limit doesn't actually apply in the same way. Plus, do you really want to have your DBAs managing this type of thing?

Rob|||But... it turns out that http://support.microsoft.com/default.aspx/kb/303879 quite clearly states that SQL2000 can only have 16K users.

But I guess the types used in sys.sysusers will cater for more.|||

I think that is refering to "database logins" and not "server logins".

The other one does say "This is because, in SQL Server 2005, you can create more than 32,767 users, groups, and roles, and 32,767 data types." This kind of implies, users/groups/roles use the same id counter, so combined you can't have more than 32,767.

MS added more than 16k of users for a reason, although I find it hard to believe someone requested that to be added because they ran out of logins. I have a hard enough time managing 100 SQL logins (excluding AD Groups), I can't imagine the headache I would have with more than that. :)

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator? All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator? All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
Hi,
I guess its by design.No UDF's too :-(
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No User Folders

The RS Administrators are able to see all folders except the users.
Why is it that users are unable to see their folders?
Please help!Does this happen in the report manager?
Perhaps the users don't have the permission to see those folders. Take a
look to the security tab on the problematic folders.|||Thanks!
However, the user did not have the proper permission to access the folder.
Again, thanks!
"F. Dwarf" wrote:
> Does this happen in the report manager?
> Perhaps the users don't have the permission to see those folders. Take a
> look to the security tab on the problematic folders.