question

TonyJK-1323 avatar image
0 Votes"
TonyJK-1323 asked Cathyji-msft commented

Admin Account - Alternative to "sa" account ?

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TonyJK-1323,

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @TonyJK-1323,

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.
145630-screenshot-2021-11-02-144711.jpg

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.