SQL Server 2022 Server Level Role Updates

By: Svetlana Golovko   |   Updated: 2022-08-17   |   Comments   |   Related: > 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

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:

• You cannot grant custom permissions to built-in server roles (except to the public role).
• Microsoft introduced user-defined server roles in SQL Server 2012. You can create a custom server role and grant server-level permissions to this role.

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.

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];

##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:

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

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

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

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.

Results when executed as the securityadmin, this works.

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

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

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.

Results for the securityadmin, this works.

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.

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

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.

Results for the securityadmin, this works.

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

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.

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

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.

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

• Learn about What's new in SQL Server 2022 (16.x) Preview.
• Review SQL Server 2022 security improvements.
• Read about Server level permissions for SQL Server 2005 and SQL Server 2008 (mssqltips.com).
• Find other security related tips here.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips