confused by server roles in SQL Server 2022

  • 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 2 months, 1 week ago by  deubel_m.
  • 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

     

  • 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.

  • This was removed by the editor as SPAM

  • 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/

  • 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.

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

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