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

Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.

I have a developer who has dbo privileges in master and msdb, and also has
VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
documentation she should be able to view the Activity Monitor, but instead
gets the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
SELECT permission denied on object 'tables', database 'mssqlsystemresource',
schema 'sys'.
SELECT permission denied on object 'partitions', database
'mssqlsystemresource', schema 'sys'.
Changed database context to '<db1>'.
Changed database context to '<db2>'.
Changed database context to '<db3>'.
Changed database context to '<db4>'.
Changed database context to '<db5>'.
Changed database context to '<db6>'.
Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229)
It popped up last week, and I read that Reporting Services running may have
an effect on it, so I stopped the service and she was able to get in with no
problem. No changes have been made to the server since I stopped the
service, but this error just started again today. No updates or patches hav
e
been installed on the server recently.
I have tried granting permission to 'tables' and 'partition' for master and
mssqlsystemresource db's, and to individual system tables but I run into
errors every time. I'm pretty new to SQL Server 2005 so please explain
everything in very simple terms!
THANKS!
Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP
1 installed.Augspies (Augspies@.discussions.microsoft.com) writes:
> I have a developer who has dbo privileges in master and msdb, and also has
> VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
> documentation she should be able to view the Activity Monitor, but instead
> gets the following error:
>...
> SELECT permission denied on object 'tables', database
> 'mssqlsystemresource',
> schema 'sys'.
> SELECT permission denied on object 'partitions', database
> 'mssqlsystemresource', schema 'sys'.
That's a tricky one. It would indicate that there are locks on tables
in msqlsystemresource. And mssqlsystemresource is unavailable to
everyone, including sa, but if you're in the sysadmin role, the
check may be bypassed so you don't see the error.
I don't really know what to suggest, but you could try my aba_lockinfo
which gives about the same information as the Actvity Monitor, but
without any GUI. You can get it from
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Maybe I would be more worried over the locks in mssqlsystemresource.
That does not really sound normal to me...
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