Is it possible to grant permissions to a user for just a certain period of time? For example, I ran into a situation the other day where I wanted to grant a user the ability to modify data in a table for only a couple hours. I haven't found a native way to do this, but thought I'd ask anyway. This would be specifically for Azure SQL Database.
WAITFOR DELAY '00:01:00' --waits for 1 minute PRINT 'FINISHED' --revoke permission code --(optional TSQL code to send an email when complete
WAITFOR TIME '08:00' --waits until 8am PRINT 'FINISHED' --revoke permission code --(optional TSQL code to send an email when complete
You could even include TSQL code to send you an email indicating the process had completed.
Keep in mind that if your SSMS session dies for any reason, the revoke code will not be executed.
A more robust (and more complex) solution would be to use Service Broker BEGIN CONVERSATION TIMER
There is no "native" way to do this. In addition to Scott's answer, you could also create a job with T-SQL commands to revoke the permissions, and schedule it to run at the specified time. This would remove the dependency on your SSMS client staying connected, ensuring that it runs, assuming that the server stays running.