October 3, 2014 at 2:31 pm
I need to temporary grant a permission to a user so he can CREATE LOGIN. And then revoke it. How to do it in T-SQL script?
Thanks
October 3, 2014 at 5:56 pm
Check this link:
http://www.sommarskog.se/grantperm.html
It'll outline how to sign a stored procedure with a certificate, from which you created a certificate login and gave the server level permissions to that login. If you're struggling with the concept after reading the link I may be able to dig up some code for you.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 5, 2014 at 6:32 pm
Thank you, that is great article, but it takes time to digest it and use it for my goal. I tried to use "EXECUTE AS" but it did not work. I hope I will find something in this article how to make my code work.
In a short, I'll try to explain what I need. We need to give users temporary access to production, bypassing applications and using SSMS or anything. These are power users, most of them know SQL language. Their regular logins are linked to a role that can do very little, and they are asking for more permissions. We agreed to give them elevated rights, but it will be just temporary, and under temporarily created login. We created a stored procedure that creates a temporary login and generates random password so they can connect through it and do what they want. Within this S.P. we are using CREATE LOGIN, and this is a big obstacle. Obviously, they don't have the rights to do it, so I put in EXECUTE AS ... clause. But unfortunately it does not work. I receive messages like "this login cannot be impersonated".
If you can suggest something or get some code from your code library, I would be very grateful. Thanks is advance.
October 5, 2014 at 8:48 pm
Slide down to the portion about signing procedures with a certificate. Ill check back again tomorrow at work.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply