Granting temporary sysadmin rights

Question

Using SQL Server 2017. I have an issue that I need to allow a Windows authenticated user the ability to read excel files that are on the network. They have public rights as well as a host of different database roles. What I am trying to do is grant temporary sysadmin rights to the system_user and execute as sa the bulk insert statement. If I get this working, I will change the execute as sa to an account that is set up for just for this purpose, but for now trying to get this working. Set up a proc that sets the rights and logs any change in rights.

I am getting a permission error regarding the sa account. I can get it to work if I manually grant sysadmin to the user, but not keen to give permanent rights to the user.

What is the best way to manage this?

Some more details. The code I am trying to execute is this....

SELECT @SQL = 'SELECT [col1],[col2],[col3] ,...... FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Mode=Read;ReadOnly=True;Database='+RTRIM(@filename)+''',
                    ''SELECT * FROM [Sheet1$B20:N5000] WHERE Type <> '''''''''')'

INSERT @local_data('SELECT [col1],[col2],[col3] ,......) EXECUTE sp_executesql @SQL

If I add the code before the OPENROWSET / sp_executesql command....

EXECUTE  AS  LOGIN ='sa'

and the user also has sysadmin rights, it code works - excel file is read correctly into SQL Server.

If the 'public' user (who does not have sysadmin rights), we get the following error;

Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission

This makes sense at this stage. The first though I had was to give temporary sysadmin rights at run time but I got this error;

Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

OK - user has few rights so I understand why these errors get thrown.

Now looking at Tony's suggestion of

GRANT ADMINISTER BULK OPERATIONS to [DOM\user]

I removed the line of code... EXECUTE AS LOGIN ='sa', removed my attempt to grant temporary rights and then granted both public and sysadmin users ADMINISTER BULK OPERATIONS rights.

For the 'public' user I get the following error.

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

With the user with sysadmin access gets the following error;

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)

Different errors and not particularly helpful. I suspect if I fixed the error for the public user, this second error would come up.

I have had a good hunt around the net, and I have done the following;

1. Made sure the Linked server Microsoft.ACE.OLEDB.12.0 has 'Disallow adhoc access' unticked.

2. On the server changed the Registry for Providers\ Microsoft.ACE.OLEDB.12.0 has a DisallowAdhocAccess REG_DWORD and set to zero.

3. Made sure that the local directory on the server C:\Users\<service account>\AppData\Local\Temp and C:\Users\Default\AppData\Local\Temp allows full control to users.

None of which have made any difference. This is a bit of an old issue as as I have never been able all the SQL Server Agent the ability to read network files and it seems to come back to the same issues. I am not a DBA (too small to have one) and being the designer and builder of a system, this stuff is falling to me.

Right now the only solution that works is to grant sysadmin rights to all users that need to process the excel files and execute as 'sa', both are things that would fail a security audit. I have no idea why this has been made this complicated and ultimately encourages bad security practices.

I have come up with an acceptable solution for now and that is to use WITH EXECUTE AS OWNER at the start of the stored procedure - i.e.

ALTER PROCEDURE [dbo].[procedure_name] 

@date                   DATETIME=       NULL,
@filedirectory          VARCHAR(MAX) =  NULL,
@filename               VARCHAR(MAX) =  NULL

WITH EXECUTE AS OWNER AS
BEGIN
BEGIN TRY.....

No need any elevated rights or GRANT ADMINISTER BULK OPERATIONS rights.

Had one error at first

The database owner SID recorded in the master database differs from the database owner SID

But the following code corrected that error

EXEC sp_changedbowner 'sa'
ALTER DATABASE [database_name] SET TRUSTWORTHY ON 

The proc does get executed as sa however. I will add an audit log to record which user ran the proc and note that the proc was run as sa. This should be good enough for now.

Answer 1

If the 'public' user who does not have sysadmin rights, we get the following error

Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission

You get this error because execute as requires impersonate permission to be able to impersonate another principal and this is correct because if no there was no sense at all to have different permissions: everyone could impersonate sysadmin.

Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

The same thing: the login that is not sysadmin cannot add someone to this role because if it was possible everyone could make himself sysadmin.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)

This is different from what you sail in your initial post. Your words were:

What I am trying to do is grant temporary sysadmin rights to the system_user and execute as sa the bulk insert statement.

Here is an example of bulk insert:

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)

And for doing this a login must have ADMINISTER BULK OPERATIONS.

To permit your non sysadmin users to access ACE.OLEDB.12 you should uncheck (or manually chenge in registry) the entry DisallowAdhocAccess and it seems to be done.

Now I cannot understand if sysadmin account accesses your file or not, on one side you said

the user with sysadmin access gets the following error

on other side you said

Right now the only solution that works is to grant sysadmin rights to all users

So sysadmin can or can not access the file? If it can, you should create a stored proc that accesses the file, create a certificate from login with sysadmin role and sign this proc with this certificate. The steps are described here:

Using Certificates to Package Server-Level Permissions

---

UPDATE

can not get past "Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.

As I said previously, ADMINISTER BULK OPERATIONS has nothing to do with "access to OLE DB provider has been denied", you should grant it in case you want your login to be able to do bulk insert.

In order to permit your public logins to access OLE DB provider you should change DisallowAdhocAccess to 0 in registry.

Maybe you did it for the wrong instance of SQL Server?

On my server I changed it under

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQL_2016\Providers\Microsoft.ACE.OLEDB.12.0

where SQL_2016 is my named instance.

Now I created a login that has no rights at all but it can access my Excel file: