asked
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.
This is what I figured. Granting server-level permissions via an impersonated login is essentially pointless, because the server-level permission usually infers more far object-level permissions on the objects you were trying to protect. *(I wouldn't say the example is broad, though. It is quite a specific example — being able to snapshot a database to which you otherwise should only be able to read data — so much so that I can't see many people needing to do it!)* Perhaps the real complaint is more that creating snapshots requires such an arbitrarily open permission such as `ALTER ANY DATABASE`. Perhaps we should lobby for some more refined ones, like `GRANT CREATE SNAPSHOT ON [the_db] TO [the_login]` which infers no object-level permissions. And, while we're lobbying, let's get `GRANT TRUNCATE ON [the_table] TO [the_user]` into the mix!
No arguments. There are a lot more knobs they could provide for tweaking security
Certificates look like the way to go, rather than GRANT IMPERSONATE. Certs look like logins but, when you sign a procedure with a cert, it executes under the context of that cert, including any server-level permissions granted to that cert. So you have a procedure that does the funky stuff, a certificate with the permissions to do the funky stuff, sign the procedure with the cert and grant EXECUTE on the procedure.
11 People are following this question.
