How to alter database on the linked server WITHOUT SYSADMIN rights?

Question

My requirement is that user performing alter CANNOT be sysadmin (it can have all other rights but not sysadmin).

I am running a query from local server which should modify a remote one

EXEC ('ALTER DATABASE REMOTEDB MODIFY FILEGROUP ftfg_REMOTEDB NAME=ftfg_REMOTEDB') at [REMOTESERVER]

This query works once I add sysadmin right to the user but without the right, it give the following error:

The server principal "USERWITHOUTSYSADMIN" is not able to access the database "REMOTEDB" under the current security context.

I am on SQL Serve 2008.

Please Help!

Answer 1

Put the EXEC command in a stored procedure and grant execute on the procedure to the user. It won't STOP a sysadmin from executing it, but it will allow others to execute it as well. Be VERY, VERY careful with this!

Answer 2

Can you allow the user to impersonate someone with the appropriate permissions?

EXEC ('ALTER DATABASE REMOTEDB MODIFY FILEGROUP ftfg_REMOTEDB NAME=ftfg_REMOTEDB') 
    AS USER = 'UserWithAppropriatePermissions'
    AT [REMOTESERVER]