0

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!

2
  • didn't you post this on stack overflow as well?
    – Thirster42
    Sep 16, 2010 at 21:04
  • I did because I was not sure how to link two accounts...it was not clear
    – Chicago
    Sep 20, 2010 at 16:47

1 Answer 1

0

The error message points toward an EXECUTE AS impersonation context issue, see Extending Database Impersonation by Using EXECUTE AS. Do you have any EXECUTE AS in your stack, any procedure that uses this clause, are you under an activated context or have you issued an EXECUTE AS in the session? More practically, can you post the result of SELECT * FROM sys.login_token executed at the moment you would call the remote linked server, exactly in the same context/session?

3
  • I don't think I have execute as. No user has sysadmin permissions (on remote or local servers). That's the requirement. And why should I need sysadmin permissons to alter database?
    – Chicago
    Sep 15, 2010 at 20:17
  • The sysadmin permission would explain why the impersonation context of EXECUTE AS cannot be extended originally. When you say 'I don't think I have', is that a guess or a verified fact? Sep 15, 2010 at 20:27
  • I just verified it and I don't have "EXECUTE AS" anywhere in my queries. That's the fact. All I am looking for is a more granular permission than sysadmin.
    – Chicago
    Sep 15, 2010 at 20:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .