Admin Account - Alternative to "sa" account ?

Question

We are using SQL Server 2016.

There is a request from Consultant - "Provide "sa" or "admin" password" so that he can set up SQL login and related settings in SQL Server.
https://support.solarwinds.com/SuccessCenter/s/article/Fix-the-required-permission-to-the-database-issue-in-configuration-wizard?language=en_US

We would like to know should we give him the "sa" details (We prefer not). If not, how should we create an admin account to him ? Should it be a SQL Account or Domain Account ? Which Server Role should be granted ?

Thanks

Answer 1

Many applications requires "sysadmin" rights during installation/update to create objects in SQL Server. The simplest is to create a new SQL login with "sysadmin" role just for that application one time usage. Then delete or disable it when it is done. Some applications store that login for updates later, so disabling it may be what you want to do until you run an update.

Answer 2

Hi @TonyJK ,

> rovide "sa" or "admin" password" so that he can set up SQL login and related settings in SQL Server.

If the user(such as Cathy) only want have permissions to create logins, SQL Server requires ALTER ANY LOGIN or ALTER LOGIN permission on the server for this operation, you do not need to add him to admin group. Refer to MS document Create a Login.

Using below T-SQL to grant ALTER ANY LOGIN permission to Cathy.

USE master;    

GRANT ALTER ANY LOGIN TO Cathy;    
GO    

> If not, how should we create an admin account to him ? Should it be a SQL Account or Domain Account ? Which Server Role should be granted ?

If user(such as Cathy) only want have permissions to create logins, you do not need to create an admin account to him. If the user also want to do some operations that need SQL admin permission. You need to add the login to admin role. Using below T-SQL to add login to sysadmin role.

Use master   

EXEC sp_addsrvrolemember Cathy, 'sysadmin';    
GO   

Or add login to sysadmin role from SSMS UI.

It does not matter whether this login is a SQL account or a domain account.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Many applications requires "sysadmin" rights during installation/update to create objects in SQL Server.

They may require - but that is only because developers have been too lazy to figure out the exact requirements.

I would be inclined to say no and ask what specific actions they need permissions for and grant accordingly. For things like creating logins, I might be inclined to ask for a list of commands to run myself. I would not grant a consultant coming in through the street ALTER ANY LOGIN.

If you have give in - because it is all shrink-wrapped - you could still set up a trace of X-Event session that traps all that happens while things are running. But you cannot filter it only for the account you give to the consultant. He may be doing the dirty stuff with any of the accounts he creates!