Skip to content

Commit 68728f4

Browse files
Merge pull request #38319 from shohamMSFT/shohamMSFT-1791496882966
Update permission for MS_SecurityDefinitionReader server-level role
2 parents 93525ab + e62874a commit 68728f4

2 files changed

Lines changed: 4 additions & 4 deletions

File tree

‎azure-sql/database/security-server-roles.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -53,7 +53,7 @@ The following table shows the fixed server-level roles and their capabilities.
5353
| `##MS_DatabaseManager##` | Members of the `##MS_DatabaseManager##` fixed server role can create and delete databases. A member of the `##MS_DatabaseManager##` role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the `dbo` user. The `dbo` user has all database permissions in the database. Members of the `##MS_DatabaseManager##` role don't necessarily have permission to access databases that they don't own. Use this server role instead of the **dbmanager** fixed database role that exists in `master`. |
5454
| `##MS_DefinitionReader##` | Members of the `##MS_DefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY DEFINITION`, respectively `VIEW DEFINITION` on any database on which the member of this role has a user account. |
5555
| `##MS_LoginManager##` | Members of the `##MS_LoginManager##` fixed server role can create and delete logins. You should use this server role over the **loginmanager** database level role that exists in `master`. |
56-
| `##MS_SecurityDefinitionReader##` | Members of the `##MS_SecurityDefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY SECURITY DEFINITION`, and respectively has `VIEW SECURITY DEFINITION` permission on any database on which the member of this role has a user account. This is a small subset of what the `##MS_DefinitionReader##` server role has access to. |
56+
| `##MS_SecurityDefinitionReader##` | Members of the `##MS_SecurityDefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY SECURITY DEFINITION`, and respectively has `VIEW SECURITY DEFINITION` permission on any database on which the member of this role has a user account. This role also grants the server-level `VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION` permission, which `##MS_DefinitionReader##` doesn't have. That permission lets members view password hashes for logins that use SQL authentication, for example, in the `password_hash` column of [sys.sql_logins](/sql/relational-databases/system-catalog-views/sys-sql-logins-transact-sql). Assign this role only to trusted administrators and security personnel who require this level of access, and follow the principle of least privilege when granting membership. |
5757
| `##MS_ServerStateManager##` | Members of the `##MS_ServerStateManager##` fixed server role have the same permissions as the `##MS_ServerStateReader##` role. Also, it holds the `ALTER SERVER STATE` permission, which allows access to several management operations, such as: `DBCC FREEPROCCACHE`, `DBCC FREESYSTEMCACHE ('ALL')`, `DBCC SQLPERF()`; |
5858
| `##MS_ServerStateReader##` | Members of the `##MS_ServerStateReader##` fixed server role can read all dynamic management views (DMVs) and functions that are covered by `VIEW SERVER STATE`, respectively `VIEW DATABASE STATE` on any database on which the member of this role has a user account. |
5959

@@ -67,7 +67,7 @@ Each fixed server-level role has certain permissions assigned to it. The followi
6767
| `##MS_DatabaseManager##` | `CREATE ANY DATABASE`, `ALTER ANY DATABASE` | `ALTER` |
6868
| `##MS_DefinitionReader##` | `VIEW ANY DATABASE`, `VIEW ANY DEFINITION`, `VIEW ANY SECURITY DEFINITION` | `VIEW DEFINITION`, `VIEW SECURITY DEFINITION` |
6969
| `##MS_LoginManager##` | `CREATE LOGIN`, `ALTER ANY LOGIN` | N/A |
70-
| `##MS_SecurityDefinitionReader##` | `VIEW ANY SECURITY DEFINITION` | `VIEW SECURITY DEFINITION` |
70+
| `##MS_SecurityDefinitionReader##` | `VIEW ANY SECURITY DEFINITION`, `VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION` | `VIEW SECURITY DEFINITION` |
7171
| `##MS_ServerStateManager##` | `ALTER SERVER STATE`, `VIEW SERVER STATE`, `VIEW SERVER PERFORMANCE STATE`, `VIEW SERVER SECURITY STATE` | `VIEW DATABASE STATE`, `VIEW DATABASE PERFORMANCE STATE`, `VIEW DATABASE SECURITY STATE` |
7272
| `##MS_ServerStateReader##` | `VIEW SERVER STATE`, `VIEW SERVER PERFORMANCE STATE`, `VIEW SERVER SECURITY STATE` | `VIEW DATABASE STATE`, `VIEW DATABASE PERFORMANCE STATE`, `VIEW DATABASE SECURITY STATE` |
7373

‎docs/relational-databases/security/authentication-access/server-level-roles.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -81,7 +81,7 @@ The following table shows fixed server-level roles introduced in [!INCLUDE [sssq
8181
| `##MS_ServerSecurityStateReader##` | Members of the `##MS_ServerSecurityStateReader##` fixed server role can read all dynamic management views (DMVs) and functions that are covered by `VIEW SERVER SECURITY STATE`. Members also hold `VIEW DATABASE SECURITY STATE` permission on any database where they have a user account. This role is a small subset of `##MS_ServerStateReader##`, which helps to comply with the *Principle of least Privilege*. |
8282
| `##MS_DefinitionReader##` | Members of the `##MS_DefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY DEFINITION`. Members also hold `VIEW DEFINITION` permission on any database where they have a user account. |
8383
| `##MS_PerformanceDefinitionReader##` | Members of the `##MS_PerformanceDefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY PERFORMANCE DEFINITION`. Members also hold `VIEW PERFORMANCE DEFINITION` permission on any database where they have a user account. This role is a subset of `##MS_DefinitionReader##`. |
84-
| `##MS_SecurityDefinitionReader##` | Members of the `##MS_SecurityDefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY SECURITY DEFINITION`. Members also hold `VIEW SECURITY DEFINITION` permission on any database where they have a user account. This role is a small subset of `##MS_DefinitionReader##`, which helps to comply with the *Principle of least Privilege*. |
84+
| `##MS_SecurityDefinitionReader##` | Members of the `##MS_SecurityDefinitionReader##` fixed server role can read all catalog views that are covered by `VIEW ANY SECURITY DEFINITION`. Members also hold `VIEW SECURITY DEFINITION` permission on any database where they have a user account. This role also grants the server-level `VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION` permission, which `##MS_DefinitionReader##` doesn't have. That permission lets members view password hashes for logins that use SQL Server authentication, for example, in the `password_hash` column of [sys.sql_logins](../../system-catalog-views/sys-sql-logins-transact-sql.md). Assign this role only to trusted administrators and security personnel who require this level of access, and follow the principle of least privilege when granting membership. |
8585

8686
## Permissions of fixed server roles
8787

@@ -102,7 +102,7 @@ The following table shows the permissions assigned to the server-level roles. It
102102
| `##MS_ServerSecurityStateReader##` | - `VIEW SERVER SECURITY STATE` | - `VIEW DATABASE SECURITY STATE` |
103103
| `##MS_DefinitionReader##` | - `VIEW ANY DATABASE`<br />- `VIEW ANY DEFINITION`<br />- `VIEW ANY PERFORMANCE DEFINITION`<br />- `VIEW ANY SECURITY DEFINITION` | - `VIEW DEFINITION`<br />- `VIEW PERFORMANCE DEFINITION`<br />- `VIEW SECURITY DEFINITION` |
104104
| `##MS_PerformanceDefinitionReader##` | - `VIEW ANY PERFORMANCE DEFINITION` | - `VIEW PERFORMANCE DEFINITION` |
105-
| `##MS_SecurityDefinitionReader##` | - `VIEW ANY SECURITY DEFINITION` | - `VIEW SECURITY DEFINITION` |
105+
| `##MS_SecurityDefinitionReader##` | - `VIEW ANY SECURITY DEFINITION`<br />- `VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION` | - `VIEW SECURITY DEFINITION` |
106106

107107
### Permissions of server roles for SQL Server 2019 and earlier
108108

0 commit comments

Comments
 (0)