Sql-server – Granting temporary sysadmin rights

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.