I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to.

I have allowed the user login ("Doc") the "Impersonate any login" permission. Doc also has impersonate rights on the "App" login, which exists despite what the error messages seem to say.

And I inserted the following statement near the beginning of the proc

EXECUTE AS LOGIN = 'App';

"App" is in the db_owner of the database that I want to update. I was running into an issue with the WITH EXECUTE AS 'App' permission on the proc I was using, because our canned audit procedure calls procedures in another database, and I had read that such a permission was confined to the current database.

But for some reason when I try the EXECUTE AS LOGIN statement it tells me that this very much used login cannot be used.

Cannot execute as the server principal because the principal "App" does not exist, 
this type of principal cannot be impersonated, or you do not have permission.

So, if this is telling me right,

  1. logins aren't the type of principal that can be impersonated, or
  2. despite the check boxes on the UI, "Doc" does not have the right to impersonate the user "App", or
  3. The login that my application uses 700,00 times a day, "does not exist".

Or this is just broken.

I'd prefer to give "sudo" type rights to limited users, but if I have to, I'll start traversing through the tree giving every single right the "limited" user needs for 24-hour access to table modifications which are kind of like the updates I want him to make. Despite that all I want him to do is call a proc that I created, update a particular column in a table in the Application database and log the result if it goes sideways.


UPDATE: Base on what srutzky says below, I tried turning Trustworthy on in a restricted database with a new user and a new login and the rights to impersonate, and it worked. I guess I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

  • Does this "App" login account have a corresponding user name in the database? If so, how about try exec as user='App' ? – jyao Nov 10 '16 at 18:44
  • Yes, login="App", user="App". From what I read user only gives them access within the database, because of our tools database, it can't be limited to just one database. – Axeman Nov 10 '16 at 18:46
up vote 3 down vote accepted

If your user does not need elevated permissions for ad hoc queries, then you can actually do better with Module Signing than with Impersonation. Impersonation comes with various complexities, especially when dealing with multiple Databases as that then requires setting TRUSTWORTHY to ON, something which is generally ill-advised as it is a security risk. Module Signing allows for more granular control over what permissions are being effectively granted, and works across Databases without needing to enable TRUSTWORTHY. I have explanations and examples of this across various answers here. One of the most recent ones, and one which includes a detailed walk-through of this cross-Database setup, is:

Stored procedure security with execute as, cross database queries, and module signing

The general concept is this:

  • Create a Certificate in master
  • Create a Login from that Certificate
  • Grant any required Server-level permissions to that Login

  • Create that same Certificate in Database A

  • Create a User in Database A from the Login
  • Grant Certificate-based User any necessary Database-level permissions
  • Sign one or more Stored Procedures / Functions in Database A with that Certificate
  • Grant real user Execute permission on the Stored Procedure(s) / Function(s)

  • Create a User in Database B from the Login

  • Grant Certificate-based User necessary permissions in Database B

In this setup, the real user/person doesn't actually have any elevated permissions. Only the code that has been signed with the Certificate has the permissions, and it can only do what the code inside it says to do. And if someone tries to change that code to do something else or more, then the module loses the signature and no longer has any elevated permissions at all. Which forces someone to resign the module, which is a good indication that the code needs to be reviewed.


UPDATE

I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

There is no real difference there. Using Impersonation, if you are creating an App User in each DB for the App Login, then it is the same to create the Certificate-based User in each DB. It is one line of code to CREATE USER [App] FROM Login [App];. BUT, if you don't need to create an App User because the App Login is in the sysadmin fixed server role, then you can just as easily place the Certificate-based Login in the sysadmin fixed server role and most likely not need to create and of the Database-level Users. However, you still end up with an account that is likely more privileged than it needs to be, just to avoid creating those Users.

  • It seems that impersonation is NOT the way I would like to do it. From experimentation, I found that all the user has to do is add EXECUTE AS LOGIN = 'App' to their script and they have all the power that the Application does!! That's like an su as opposed to a sudo. {shudder} – Axeman Nov 11 '16 at 15:21
  • @Axeman EXACTLY! This is the beauty of Module Signing, since Certificate-based Logins / Users cannot be impersonated via EXECUTE AS and cannot connect to the instance / database. Module Signing is the most secure way to extend permissions! Because you are giving the permissions to code (which is specific in what it does) rather than to people (who can do anything they are allowed to do). Hence my final sentence in the UPDATE about having a more priviledged account than you need / want when using Impersonation :-) – Solomon Rutzky Nov 11 '16 at 15:24

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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