asked
I need a SQL Statement that elevates permissions and then lowers on a time limit
I have a scheduled task that runs two SQL statements: One that elevates a users' role from public to dbcreator and setupadmin and the other one that lowers it back to public. I would like to add to the first one a time limit so that I won't need two scheduled tasks. Is there a way to put in the statement to elevate role for 4 hours and then lower back?
If you know it will definitely need to be 4 hours, you could use `waitfor`: exec sp_addrolemember @rolename = 'dbcreator', @membername = 'SOMEUSER'; exec sp_addrolemember @rolename = 'setupadmin', @membername = 'SOMEUSER'; waitfor delay '04:00:00' exec sp_droprolemember @rolename = 'dbcreator', @membername = 'SOMEUSER'; exec sp_droprolemember @rolename = 'setupadmin', @membername = 'SOMEUSER'; As long as you don't put the entire thing in an explicit transaction, there shouldn't be any major problems.
...assuming that the server instance doesn't barf during that 4h delay... ;-)
Paranoid? Me? Is that what they're saying about me?
Another mechanism would be to use a SQL Agent job. You can set up the first job to execute, and then enable the second job and set the time when it should execute. Use multiple steps within the first job to take care of everything.
Well, it has to be able to read when the user logs into the computer in order to execute. The whole idea is so we can grant temp permissions to our install group and then the permissions will expire. We have so many installs going on and only 1 DBA. (sorry I had to create a temp account because i forgot my login)
Thank you! I created a SQL Agent Job that granted permissions and delayed it (only did 2 minutes to test) and everything worked. I really appreciate the help!
12 People are following this question.
