As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

Can you grant access through a job that runs at a specified time and remove this access through another job with the end period?

The short answer is yes.  But rather than having just the short answer, let’s talk more about this requirement.

Grant Permissions Through SQL Agent Job

Expanding on the question, the requirement is to be able to grant and revoke permissions based on time.  With SQL Server Agent, this would be fairly easy to accomplish.  With one job, grant the permissions and with the other revoke those permissions.  In it’s simplest form, the solution would consist of just two jobs steps.

Of course, as DBAs, we know that nothing can ever be that easy.  Once we had the solution in place, the requirements would change.  Instead of being able to grant and revoke a single set of permissions, the requirement would likely evolve into the need to grant and revoke various sets of permissions through out the day.

Considering these expanded requirements, one option is to make the solution more dynamic.  Instead of just a couple jobs to grant and revoke permissions, the process can be expanded to leverage a table that lists the permissions that need to be granted and revoked and the times to perform the operations.  When the SQL Agent job executes, simply loop through each of the permissions listed and either grant or revoke them based on the time.

Leveraging the second solution, provides an additional benefit.  For some permissions, DBAs could allow users to request and receive temporary access.  This can alleviate some of the work for the DBAs while granting users the access they require as they require it.  Also, this would minimize user access when they don’t require it while allowing them elevated permissions for limited times.  A method to help prevent unintended actions on the databases.

Summary

As mentioned, it is possible to grant permissions through SQL Agent jobs.  It’s just a matter of deciding how to implement the requirements and looking forward to see how the requirements will evolve.  What about you?  Do you currently grant/revoke permissions for users through SQL Agent jobs?  Do you have any concerns with doing so?