confused by server roles in SQL Server 2022

Question

confused by server roles in SQL Server 2022

deubel_m

SSC-Addicted

Points: 465
More actions
August 19, 2024 at 9:50 am

#4447198
Hello there,
I came across a, from my point of view, weird behaviour regarding server roles and permissions in SQL Server 2022. The scenario:
sql server account user01 with the following server level roles:
- ##MS_DatabaseManager##
- ##MS_ServerPerformanceStateReader##
- ##MS_ServerStateReader##
- public
Now, when I right click in SSMS 20.2 on the database engine node server01 (connected with user01) I see the sql server service actions enabled (and the actions work). Despite the user doesn't have any permissions execpt above.
But, in the same SSMS I am connected to another sql server server02 with a domain account which has the sysadmin role on server01.
Testet this scenario with SSMS 19.3 and the actions are disabled.
So, I am confused...
- This topic was modified 3 months ago by deubel_m.
August 20, 2024 at 6:25 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Arvind Toorpu SSC Enthusiast Points: 179 More actions · Answer 1

I hope the text below helps you understand.

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)

SQL Server 2019 and previous versions provided nine fixed server roles. The permissions that are granted to the fixed server roles (except public) can't be changed. Beginning with SQL Server 2012 (11.x), you can create user-defined server roles and add server-level permissions to the user-defined server roles. SQL Server 2022 (16.x) comes with 10 extra server roles that have been designed specifically with the Principle of Least Privilege in mind, which have the prefix ##MS_ and the suffix ## to distinguish them from other regular user-created principals and custom server roles. Those new roles contain privileges that apply on server scope but also can inherit down to individual databases (except for the ##MS_LoginManager## server role.)

Here is more info on the new roles introduced in 2022:

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16

deubel_m SSC-Addicted Points: 465 More actions · Answer 2

Arvind Toorpu wrote:

I hope the text below helps you understand.
SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)
SQL Server 2019 and previous versions provided nine fixed server roles. The permissions that are granted to the fixed server roles (except public) can't be changed. Beginning with SQL Server 2012 (11.x), you can create user-defined server roles and add server-level permissions to the user-defined server roles. SQL Server 2022 (16.x) comes with 10 extra server roles that have been designed specifically with the Principle of Least Privilege in mind, which have the prefix ##MS_ and the suffix ## to distinguish them from other regular user-created principals and custom server roles. Those new roles contain privileges that apply on server scope but also can inherit down to individual databases (except for the ##MS_LoginManager## server role.)
Here is more info on the new roles introduced in 2022:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16

Thanks for the reply but I am aware of it and I know what server-level roles are. If you don't mind reread my post.

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

What is there to be confused about?

You have two servers, and two accounts. One is a sysadmin on one server, the other is not.

Sysadmin is the highest level of permissions on a server.

##MS_DatabaseManager##, ##MS_ServerPerformanceStateReader##, ##MS_ServerStateReader##, and public have very few rights.

https://techcommunity.microsoft.com/t5/azure-sql-blog/new-server-roles-for-azure-sql-database-and-sql-server-2022-in/ba-p/3428433

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

deubel_m SSC-Addicted Points: 465 More actions · Answer 4

Michael L John wrote:

What is there to be confused about?
You have two servers, and two accounts. One is a sysadmin on one server, the other is not.
Sysadmin is the highest level of permissions on a server.
##MS_DatabaseManager##, ##MS_ServerPerformanceStateReader##, ##MS_ServerStateReader##, and public have very few rights.
https://techcommunity.microsoft.com/t5/azure-sql-blog/new-server-roles-for-azure-sql-database-and-sql-server-2022-in/ba-p/3428433

It's NOT about what server roles are. I know what server roles are. Please just read my post carefully. If you don't understand the problem. Just ask!

Obviously I didn't line out enough the problem. Next try. Two servers server01, server02. Two accounts (sql user user01, domain\account).

user01: server roles on server01 (##MS_DatabaseManager##,##MS_ServerPerformanceStateReader##,##MS_ServerStateReader##,public)

domain\account: sysadmin on server01 and server02

SSMS 20.2: connected with user01 to server01,

connected with domain\account(sysadmin) to server02

Forgot: I am connected to both servers at the same time.

So here is the problem: I can shutdown/start/stop the sql server from SSMS with user01 on server01. Remember: user01 on server01 is only member of ##MS_DatabaseManager##,##MS_ServerPerformanceStateReader##,##MS_ServerStateReader##,public.

The SSMS runs under domain\account, which is sysadmin on server01.