permissions for OPENROWSET

[Jay Meerdink]

I'm using SQL Server 2008 and with sa, this works fine

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=q:\teste.xlsx', [zz$])

Succeeds for sa, but not a domain user. I've started morphing the
domain user login and db user into an sa-type creature, but before I
get too far and then have problems reverting, I'd like to bounce the
issue off the community.

The permissions paragraph on OPENROWSET says:

Permissions

OPENROWSET permissions are determined by the permissions of the user
name that is being passed to the OLE DB provider. To use the BULK
option requires ADMINISTER BULK OPERATIONS permission.

Can anybody narrow this down to specific db user permissions for the
domain login's db user?

Thanks!

[Jay Meerdink]

I didn't even think of the filesystem permissions requirements. SQL
Server runs under a domain account that has perms in the filesystem.
I created a new SQL login from an AD login with appropriate
permissions in the filesystem and compared the query results from that
guy. This is what happens now in SSMS:

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been
denied. You must access this provider through a linked server.

Is that the same thing as the sp_configure 'Ad Hoc Distributed
Queries' ? Because I already set that to 1 just to get the query to
run as sa.

It sounds more like a non-SQL perms problem on the driver itself.

[Jay Meerdink]

So I ran notepad and tried to save a file c:\test.txt and it failed.
Maybe that's UAC instead of NTFS? (The server is Win 2008 R2.)

I was able to save the test.txt to my domain account's local documents
folder. I moved the spreadsheet to the same domain user documents
folder.

The query can't get to the spreadsheet. When I run SSMS as
administrator, open the query as my domain account, it works.

Is there a way to elevate the query within SSMS?

What's the difference between a domain security context and elevated
UAC?

[Erland Sommarskog]

Jay Meerdink (jay.me...@gmail.com) writes:
> I was able to save the test.txt to my domain account's local documents
> folder. I moved the spreadsheet to the same domain user documents
> folder.
>
> The query can't get to the spreadsheet. When I run SSMS as
> administrator, open the query as my domain account, it works.
>
> Is there a way to elevate the query within SSMS?

No. SSMS is just another Windows application.

> What's the difference between a domain security context and elevated
> UAC?

That's more a Windows question than an SQL Server question. But I guess
that if you don't runs as admin, that you have fewer bits set in your
security token, or whatever they use.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx