SQL Server 2022 Server Level Role Updates

By:   |   Updated: 2022-08-17   |   Comments   |   Related: More > SQL Server 2022


Problem

The new SQL Server version (SQL Server 2022 Preview) is now available and there are some improvements related to security. There are also a couple of new built-in (fixed) server-level roles that were introduced in SQL Server 2022. Separation of duties and a principle of minimal permissions are SQL Server security best practices and we will cover how to use these new security features.

Solution

Before SQL Server 2022, there were nine fixed server roles. Here are the results of running sp_helpsrvrole security system stored procedure on SQL Server 2019:

USE [master]
GO
EXEC sys.sp_helpsrvrole
GO
Pre-2022 fixed server roles. sp_helpsrvrole output.

Note: As per this Microsoft article, the public role is not included in the procedure output results:

"All logins are a member of public (role). sp_helpsrvrole does not recognize the public role because, internally, SQL Server does not implement public as a role."

Other things that you need to remember about fixed server roles:

Read Understanding SQL Server Fixed Server Roles to learn more about SQL Server fixed server roles.

When we run the same procedure on SQL Server 2022, notice the Server Roles in SSMS don't match the results of sp_helpsrvrole execution. This might change in the final SQL Server 2022 release.

SQL Server 2022 Server Roles and sp_helpsrvrole output.

We can run this query on SQL Server 2022 to list all fixed server roles, including the new built-in server roles:

USE [master]
GO
SELECT [name] AS Role_Name,
       [type_desc] AS Role_Type,
       CASE WHEN [name] LIKE '##%' THEN 'New To SQL Server 2022 Role'
            ELSE 'Existed Prior to SQL Server 2022 Role'
       END AS New_Old
FROM sys.server_principals
WHERE [type_desc] = 'SERVER_ROLE'
ORDER BY [name];
SQL Server 2022 New fixed server roles

##MS_LoginManager## Role vs. securityadmin Role

One of the new SQL Server 2022 built-in server roles, ##MS_LoginManager##, seems very similar at first to the securityadmin server role. How do we know which role is more secure to use for login creation and what are the differences between the old securityadmin and the new ##MS_LoginManager## roles?

As per this Microsoft article, we should be cautious when granting the securityadmin role to the users. The same article even warns to treat the securityadmin role as the sysadmin role:

Securityadmin description and warning

Let's compare these server roles permissions with the old sp_srvrolepermission system stored procedure:

USE [master]
GO
EXEC sys.sp_srvrolepermission N'##MS_LoginManager##'
GO
EXEC sys.sp_srvrolepermission N'securityadmin'
GO
Compare permissions with sp_srvrolepermission

We will create two logins with server roles permissions and two regular logins with no initial permissions to view what server roles members can do with them:

USE [master]
GO

-- create securityadmin
CREATE LOGIN  [_test_secadmin] WITH PASSWORD=N'Replace_with_strong_P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE securityadmin ADD MEMBER [_test_secadmin]
GO

-- create ##MS_LoginManager##
CREATE LOGIN [_test_loginMgr] WITH PASSWORD=N'Replace_with_strong_P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [##MS_LoginManager##] ADD MEMBER [_test_loginMgr]
GO

-- create test login
CREATE LOGIN [_test1] WITH PASSWORD=N'Replace_with_strong_P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [master]
GO
CREATE USER [_test1] FOR LOGIN [_test1]
GO
CREATE LOGIN [_test_new] WITH PASSWORD=N'Replace_with_strong_P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

Let's login with _test_secadmin login (securityadmin role member) and see the results of the fn_my_permissions system security function:

USE [master]
GO
SELECT SUSER_SNAME() as MyName, * FROM sys.fn_my_permissions(NULL, NULL);
GO
fn_my_permissions System Security Function results for securityadmin

After logging in as the _test_loginMgr login (##MS_LoginManager## role member), we can see that the server-level permissions are the same:

fn_my_permissions System Security Function results for ##MS_LoginManager##

To list all built-in permissions of the server, we can run the fn_builtin_permissions system security function:

USE [master]
GO
SELECT * FROM sys.fn_builtin_permissions(default);
GO

Testing the Server Roles Permissions

Now we will test some permissions that the securityadmin role member has, and the ##MS_LoginManager## server role member does not.

Grant Create Database to Other Logins

We will run this script with each of our logins/server roles members:

USE [master]
GO
PRINT SUSER_SNAME()
GO 
GRANT CREATE DATABASE TO  [_test1]
GO

Results when executed as the ##MS_LoginManager##, this does not work.

Grant Create Database results for ##MS_LoginManager##

Results when executed as the securityadmin, this works.

Grant Create Database results for Securityadmin

Note: Even though the ##MS_LoginManager## member has permissions to create logins, it cannot explicitly grant this permission to other logins:

Grant Create Login results for ##MS_LoginManager##

It can add logins to the ##MS_LoginManager## server role that it belongs to:

Add login to server role results for ##MS_LoginManager##

Grant Backup Database to Other Logins

Now we will test database-level permissions.

We will create a database and database users:

USE [master]
GO
CREATE DATABASE [_test_2022]
GO
USE [_test_2022]
GO
CREATE USER [_test_loginMgr] FOR LOGIN [_test_loginMgr]
GO
CREATE USER [_test_secadmin] FOR LOGIN [_test_secadmin]
GO
CREATE USER [_test1] FOR LOGIN [_test1]
GO

Note: Server roles members do not have any additional permissions on the new database, only users in the database.

Now we will run this script with the securityadmin and then with the ##MS_LoginManager##:

USE [_test_2022]
GO
PRINT SUSER_SNAME()
GO
GRANT BACKUP DATABASE TO [_test1]
GO

Results for the ##MS_LoginManager##, this does not work.

Grant backup database results for ##MS_LoginManager##

Results for the securityadmin, this works.

Grant backup database results for Securityadmin

So, we can see that even though the securityadminn role member was not granted any additional permissions on the new database, it can still grant some permissions to users' databases if it has a user account on that database.

Grant View Definition to Other Logins

We will run this script with each of our logins/server roles members:

USE [master]
GO
PRINT SUSER_SNAME()
GO
GRANT VIEW ANY DEFINITION TO [_test1]
GO

Results for the ##MS_LoginManager##, this does not work.

Grant  View Any Definition results for ##MS_LoginManager##

Results for the securityadmin, this works. Server-level permission is granted!

Grant  View Any Definition results for Securityadmin

Grant or Deny Windows Login

We will run this script with each of our logins/server roles members:

USE [master]
GO
PRINT SUSER_SNAME()
GO
EXEC sys.sp_grantlogin N'domain1\WinSQLLogin1'
GO

Results for the ##MS_LoginManager##, this does not work.

Sp_grantlogin execute results for ##MS_LoginManager##

Results for the securityadmin, this works.

Sp_grantlogin execute results for Securityadmin

Note: The ##MS_LoginManager## member can still create Windows logins with this script:

USE [master]
GO
PRINT SUSER_SNAME()
GO
CREATE LOGIN [domain1\WinSQLLogin1] FROM WINDOWS
GO
Create Login results for ##MS_LoginManager##

Read Error Log

In this test, we will log in to SQL Server Management Studio (SSMS) with securityadmin and then with ##MS_LoginManager## role member and try to view SQL Server logs.

Read error log with SSMS by 2 logins

The member of the securityadmin server role can view the error logs, and the member of the ##MS_LoginManager## will get this error:

SSMS error for ##MS_LoginManager## when trying to view SQL Server Logs (Error: 27219 - Caller does not have permissions to execute the stored procedure

Conclusion

As we can see, the new role is stricter and limited to logins management only. It cannot manage database-level permissions compared to the securityadmin role and cannot grant server-level permissions that it does not have itself. It also does not have permissions to some of the security system stored procedures.

Here is a table summarizing the tests we performed in this tip.

Test Name Securityadmin results ##MS_LoginManager## results
Grant Create Database to other Logins Can grant Can't grant

(Msg 4613, Level 16, State 1, Line 14 Grantor does not have GRANT permission.)
Grant Backup Database to other Logins Can Grant when login has a user in a user's database Can't grant

(Msg 4613, Level 16, State 1, Line 14 Grantor does not have GRANT permission.)
Grant View Definition to other Logins Can grant Can't grant

(Msg 4613, Level 16, State 1, Line 14 Grantor does not have GRANT permission.)
Grant or Deny Windows Login Can grant Can't grant with system stored procedures

(Msg 15247, Level 16, State 1, Procedure sys.sp_grantlogin, Line 13 [Batch Start Line 33] User does not have permission to perform this action.).

Can grant with "CREATE LOGIN" statement.
Read Error Log Can view the logs Can't view the logs

(Caller does not have permissions to execute the stored procedure. (Microsoft SQL Server, Error: 27219))

Be aware of the securityadmin abilities and treat it as a login that is as privileged as a sysadmin.

If a new login only requires adding/updating/deleting logins, you should use the new ##MS_LoginManager## built-in role.

Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips


Article Last Updated: 2022-08-17

Comments For This Article

















get free sql tips
agree to terms