What is the point of GRANT IMPERSONATE over just granting the server-level permission?
No really, I think I understand the principal of it, but what is the point? Say, for example, I have a group of users who need to be able to create snapshots of a database `RODB` to which they don't own and would ordinarily only be in `db_datareader`. This group is bound to a server-level login `[MYDOMAIN\Snapshotters]`. We can write them a procedure `sp_create_snapshot` that takes care of the heavy lifting and `GRANT EXECUTE` on that procedure. But, unless they have `CREATE/ALTER ANY DATABASE` server privilege, they can't create the snapshot, anyway. So, we create a new server-level Login `alterdb` which has `ALTER ANY DATABASE`, bind it to a database-level user `alterdb` and `GRANT IMPERSONATE ON LOGIN::[alterdb] TO [MYDOMAIN\Snapshotters]`. And also lock down the `alterdb` login, so it has a long random password and is disabled for login, for good measure. Then we alter the `sp_create_snapshot` procedure to do the snapshot creation in the context of that `alterdb` user: `EXECUTE ('CREATE DATABASE...') AS LOGIN='alterdb'`. This *works*, but how is it any better than just granting `ALTER ANY DATABASE` to the `[MYDOMAIN\Snapshotters]` group, besides the additional level of indirection? They can still get into that context, at any time, by using the same `EXECUTE ('sql') AS LOGIN='alterdb'`, without any extra effort, so they might as well have `ALTER ANY DATABASE`, which means they might as well have ownership of `RODB`. So what is the point?
In your example, you're right. No point at all. The general concept is to be able to grant a specific set of behaviors in a very limited fashion. Your example is far too broad. Narrow it way down to the ability to execute a single procedure or something along those lines and then it makes more sense. It's still going to be an extreme edge case though. I still haven't had a verifiable need for it with any of the systems I've been administering. But, when you need that scientifically calibrated impact wrench, it's nice to have it. And that's really what this is.