SQL Server – Recommended Permissions for Creating New SQL Accounts

sql server

Seeking some recommendations on how to handle a SQL account, that will be creating additional SQL accounts in the future…

Scenario:

Our Admin production app connects to SQL using an account called: Admin_Main, as stated in the connection string. When somebody creates a new user in the Admin app and assigns them a Reporting license, a new SQL Account is created on the instance, for the new user. The Admin_Main account is used to create this account.

I, of course, do not want to add more permissions to the Admin_Main account than needed, so my question is;

What are the permissions that I should add to this account, so that it's able to create new SQL Server accounts, but doesn't have Admin permissions on the instance?

Thanks in advance!

Best Answer

You could assign the server role securityadmin to the Admin_Main account.

The securityadmin server role is documented as:

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

Reference: Server-Level Roles (Microsoft | Docs)

Let's give that a try and see how far we can get...

Creating SQL Server Login Admin_Main

We'll create a new SQL Server Login and assign that user the securityadmin server role:

USE [master]
GO
CREATE LOGIN [Admin_Main] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [Admin_Main]
GO

This is all done with an account that has the sysadmin server role and the result is:

Command(s) completed successfully.

Login With Admin_Main

You can then open up a new database query window using the Admin_Main SQL Serer Login and the corresponding password. Once you have done that...

Assigning Higher Privileges

...your new user is certainly going to try and increase his/her privileges by adding himself/herself to the sysadmin server role:

ALTER SERVER ROLE [sysadmin] ADD member [Admin_Main]

This results in the following:

Msg 15151, Level 16, State 1, Line 3
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

So the SQL Server Login can't assign himself higher privileges.

Creating a New User (Or: How Far Can I Get?)

Seeing as the Admin_Main is now allowed to create new SQL Server Logins, let's create a new user while still logged in as Admin_Main and add some server level roles. We'll do this in multiple steps to find out when assigning SQL Server roles will fail:

Create Login

Input

USE [master]
GO
CREATE LOGIN [New_User] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

Output

Command(s) completed successfully.

Assign securityadmin Role

Input

ALTER SERVER ROLE [securityadmin] ADD MEMBER [New_User]
GO

Output

Command(s) completed successfully.

Assign sysadmin SQL Server Role

Input

ALTER SERVER ROLE [sysadmin] ADD MEMBER [New_User]
GO

Output

Msg 15151, Level 16, State 1, Line 13
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

Conclusion

You will have to assign the SQL Server role securityadmin to the Admin_Main SQL Server Login to allow this account to create new SQL Server Logins and/or Windows Authenticated SQL Server Logins.

...and you can't assign permissions/privileges to other accounts that your initial accounts doesn't already possess.

Related Question