I've seen numerous posts over the years that ask for a way to allow some "regular" user to run a privileged operation. The two most common items are running jobs and changing security. These posts always invite a number of solutions and lots of cautionary notes about the dangers of allowing users to fundamentally alter the way your instance behaves.

I do understand the reasons why people want to allow this. There are often administrative things that we can delegate to a user that we can trust them to do. They know how security should be managed or they know when to run a job. In essence, we are using them as proxies so that we don't get overwhelmed with requests for simple tasks.

At the same time, we don't want to give them access from SSMS to do something like add a user to a role or create a login. There is too much chance that they'll do something wrong. Worse, they might actually make a mistake and grant someone too many privileges or remove another privileged user from a system.

What I've done in these cases is try to implement a simple proxy system that doesn't directly allow the user to do something, but they can ask the server to do it. What do I mean? Here's an example. I'd create two tables: one for data and one for logging. In the data table, a user(s) has rights to insert some data, like the name for a new login. I then use a process (usually an Agent job) that reads this table, performs some action, and then logs the item in my logging table. For something like a login, this would likely generate a strong password and send it to someone.

I like to be able to run checks, avoiding known issues, preventing things like changing a password for "sa", or some other issue. I also like having my own log since I've known too many users, including administrators, that forget what command they just ran. Using a process for simple tasks allows you to audit what is taking place.

I've seen similar systems for sysadmins implemented as Slackbots that allow them to change passwords or run some job with a command in a channel, rather than directly connecting to machines. The idea, however, is the same as what we try to do in DevOps. Build a process, improve it over time, and use automation to handle the simple tasks that computers do better than humans.