We’re thinking of creating a web site where a manager can provide temporary access to a person for a limited set of data in our production SQL azure database for production support. Auditing is mandatory. The support user would need to be their domain account so we can audit the activity. Write it to a table, add the user to an AAD group that has needed permissions, a logic app would remove that user from the group after x minutes.
Is there an easier way or better solution for this?
We looked, and didn’t find a packaged product that met our similar needs. We wanted to be able to get away from having so many developers with
dbowner (and even
sysadmin) access to databases and instances.
We ended up writing our own desktop application. It doesn’t quite match your requirements, but it is close.
The user runs the application, which authenticates them via Windows authentication. There is a lookup that then determines which instances/databases they can request elevated permissions to. And how far they can elevate. All of which has to be approved ahead of time by their supervisor. There are four options for elevation level: (lowest to highest )
- R+W – Read and write only on a specific database.
- DDL and R+W – You can create objects, change schema on a specific database as well as read and write.
- [DB]OWNER – Full access to a specific database.
- SYSADMIN – Grants you full access to the instance and all the databases.
They also have to fill out a field to explain why they need the elevated permissions. The program then creates a temporary SQL login for them that they can use to do their elevated work. (We tried to do it by adding them to an Active Directory group, but something didn’t work right. I don’t remember what it was. We’ll try that again some day.)
It logs the fact that they asked for elevated rights to a table that we can then run reports from. We also set up our third-party compliance auditing tool to specifically audit all activities from these temporary elevated logins. The process also sends an e-mail message to the person’s supervisor whenever they request elevated access.
We then have a scheduled job that removes these temporary logins at 6:00 a.m. the next day.