1

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.

1

2 Answers 2

2

A very simple approach would be to open up an SSMS query window that's connected to the instance you want and use either WAITFOR DELAY or WAITFOR TIME

WAITFOR DELAY '00:01:00' --waits for 1 minute
PRINT 'FINISHED' --revoke permission code
--(optional TSQL code to send an email when complete

or ------

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

2

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.

4
  • I didn't think that Azure supported the SQL Server agent, so I did not include that in my answer. I don't actually work on Azure, so you may know better than me. Oct 20, 2017 at 13:06
  • If you have any server (or even your workstation) that can connect to it--you could create a Windows scheduled task to run a PowerShell script. I don't have experience with Azure SQL, but we have cloud Exchange and we do quite a few things from on-premise servers running scheduled tasks. Once you figure out how to do it then you'd just need to change the task schedule and the .sql script to according to the situation. Oct 20, 2017 at 13:15
  • @ScottHodgin - You are correct. Azure doesn't support the SQL Agent, yet. However, as soon as Managed Instances goes live, it will. Oct 20, 2017 at 13:17
  • Alternatively, you can use a local SQL Server agent job to invoke SQLCMD to talk to Azure and do what @TonyHinkle is suggesting - see How to Run Scheduled Jobs in Azure SQL Databases Oct 20, 2017 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.