0

I've implemented a process to import data to a database that needs to set IDENTITY_INSERT ON and OFF.

However, to have this permission the user must own the table or have ALTER permission on the table.

As changing the process is not possible, my question is if it is possible to limit the grants given to a user when giving him the ALTER permission.

i.e., at the end of the day I would like to have an user with ALTER permission that can set IDENTITY_INSERT ON and OFF but CANNOT add/remove columns nor similar stuff.

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eduardo Poças
  • 19
  • 2
  • 2
    Can you replace the `SET IDENTITY_INSERT` statements with something else, like a stored procedure call? – Jeroen Mostert Oct 03 '17 at 11:17
  • 1
    Can you encapsulate the import process in a stored proc? If so, you could use module signing with a certificate user with the needed permissions. The invoking user would only need permissions to execute on the proc. – Dan Guzman Oct 03 '17 at 11:56
  • @DanGuzman: this should probably be a question on its own, but is there any reason (security or otherwise) to prefer the certificate hoopla over simply creating a separate user `WITHOUT LOGIN`, `GRANT ALTER` to that, and using `EXECUTE AS` on the sproc? – Jeroen Mostert Oct 03 '17 at 11:58
  • @JeroenMostert, Erland Sommarskog discusses these considerations in his thorough article on the subject: http://www.sommarskog.se/grantperm.html. I agree certs are a little more work but more flexible and robust. – Dan Guzman Oct 03 '17 at 12:09
  • @DanGuzman: I've actually read Erland's article (well, all of them, actually); the main problem with certificates is the odious requirement for re-signing after every change, which I haven't yet found to outweigh the "more flexible and robust" advantages (they are only *really* necessary if you require the original user's identity be used for operations). Yes, you can solve that with even more plumbing, but if all you need to do is grant *one* particular permission for *one* app... ...that said, this is going off topic since there's not even an answer yet. :-) – Jeroen Mostert Oct 03 '17 at 13:22

0 Answers0