Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!

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

1 Answer 1

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?

share|improve this answer
    
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 '10 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? –  Remus Rusanu Sep 15 '10 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 '10 at 20:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.