CODEWITHSUNDEEP
sql-serverpermissionslinked-serveralter-tableremote-server
Chicago
Chicago

Reputation: 1629

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

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!

Upvotes: 1

Views: 4059

Answers (3)

Chicago
Chicago

Reputation: 1629

After much research: This is not possible:(

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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]

Upvotes: 0

Nathan Wheeler
Nathan Wheeler

Reputation: 5932

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!

Upvotes: 0

Related Questions