microsoft / microsoft/sqlmanagementobjects
SSMS <-> SMO Bug listing db_datareader role members
まだ誰も着手していません。
- 主要言語
- C#
- スター
- 143
- フォーク
- 28
- PR マージ指標
- 30日以内にマージされた PR はありません
説明
@shueybubbles Ran into this Friday night during a production deploy that re-organized permissions... Came up with a simple repro:
Try the following database:
CREATE DATABASE [Test]
GO
USE [Test]
GO
CREATE ROLE Readers
GO
ALTER ROLE db_datareader ADD MEMBER [Readers]
GO
CREATE USER [MYDOMAIN\michael.miller] WITHOUT LOGIN
GO
ALTER ROLE Readers ADD MEMBER [MYDOMAIN\michael.miller]
GO
/* Output Table formatted with GitHub Markdown below */
SELECT * FROM sys.database_principals WHERE name = 'db_datareader'
/* Output Table formatted with GitHub Markdown below */
SELECT p.name FROM sys.database_role_members rm
JOIN sys.database_principals p ON rm.member_principal_id = p.principal_id
WHERE rm.role_principal_id = 16390
| name | principal_id | type | type_desc | default_schema_name | create_date | modify_date | owning_principal_id | sid | is_fixed_role | authentication_type | authentication_type_desc | default_language_name | default_language_lcid | allow_encrypted_value_modifications |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| db_datareader | 16390 | R | DATABASE_ROLE | NULL | 2003-04-08 09:10:42.363 | 2009-04-13 12:59:14.467 | 1 | 0x01050000000000090400000000000000000000000000000006400000 | 1 | 0 | NONE | NULL | NULL | 0 |
| name |
|---|
| Readers |
When I go into SSMS and right click on Properties for the db_datareader role, I see the following INCORRECT mappings that do not match the output of the last two queries above:

I think there may be a separate bug here, observable in SSMS but not sure what SMO calls SSMS is making (is there a way to force SSMS to log that to the telemetry window? Telemetry window doesn't have super detailed logging by default).
The SSMS "Output Window --> Object Explorer" output I get is:
[2021-01-11T11:05:14.2162761-05:00 End Query] URN:Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role
Elapsed time:10.7294 ms
Query: SELECT
rl.name AS [Name],
'Server[@Name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Role[@Name=' + quotename(rl.name,'''') + ']' AS [Urn],
rl.principal_id AS [ID],
CAST(CASE WHEN rl.principal_id > 16383 AND rl.principal_id < 16400 THEN 1 ELSE 0 END AS bit) AS [IsFixedRole]
FROM
sys.database_principals AS rl
WHERE
(rl.type = 'R')
ORDER BY
[Name] ASC
which produces the following output table:
| Name | Urn | ID | IsFixedRole |
|---|---|---|---|
| db_accessadmin | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_accessadmin'] | 16385 | 1 |
| db_backupoperator | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_backupoperator'] | 16389 | 1 |
| db_datareader | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_datareader'] | 16390 | 1 |
| db_datawriter | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_datawriter'] | 16391 | 1 |
| db_ddladmin | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_ddladmin'] | 16387 | 1 |
| db_denydatareader | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_denydatareader'] | 16392 | 1 |
| db_denydatawriter | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_denydatawriter'] | 16393 | 1 |
| db_owner | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_owner'] | 16384 | 1 |
| db_securityadmin | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='db_securityadmin'] | 16386 | 1 |
| public | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='public'] | 0 | 0 |
| Readers | Server[@Name='---SCRUBBED---']/Database[@Name='Test']/Role[@Name='Readers'] | 5 | 0 |
and the SSMS "Output Window -> Telemetry" output view has:
[2021-01-11T11:05:07.7326467-05:00] sql/ssms/explorerhierarchynode/buildchildren
DataModel.Action.DurationInMilliseconds = 185.0194
SQL.SSMS.ObjectExplorer.Entity = Server/DatabasesFolder
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:09.2765405-05:00] sql/ssms/explorerhierarchynode/buildchildren
DataModel.Action.DurationInMilliseconds = 44.0064
SQL.SSMS.ObjectExplorer.Entity = Server/Database
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:11.3234256-05:00] sql/ssms/explorerhierarchynode/buildchildren
DataModel.Action.DurationInMilliseconds = 3.982
SQL.SSMS.ObjectExplorer.Entity = Server/Database/DatabaseSecurityFolder
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:12.6408375-05:00] sql/ssms/explorerhierarchynode/buildchildren
DataModel.Action.DurationInMilliseconds = 0.538
SQL.SSMS.ObjectExplorer.Entity = Server/Database/RolesFolder
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:14.2193114-05:00] sql/ssms/explorerhierarchynode/buildchildren
DataModel.Action.DurationInMilliseconds = 27.0348
SQL.SSMS.ObjectExplorer.Entity = Server/Database/DatabaseRolesFolder
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:16.9081278-05:00] sql/ssms/explorerhierarchynode/setupmenuparent
DataModel.Action.DurationInMilliseconds = 0
SQL.SSMS.ObjectExplorer.Entity = Server/Database/Role
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:16.9191466-05:00] sql/ssms/explorerhierarchynode/getmenuitems
DataModel.Action.DurationInMilliseconds = 8.9941
SQL.SSMS.ObjectExplorer.Entity = Server/Database/Role
SQL.SSMS.ObjectExplorer.IsSqlDW = False
SQL.SSMS.ObjectExplorer.ServerType = Standalone
[2021-01-11T11:05:21.8648667-05:00] /sql/ssms/dialog
SQL.Dialog.Action = Invoke
SQL.Dialog.Name = DBRoleProp
コントリビューションガイド
このリポジトリのコントリビューションガイドは索引されていません
はじめの一歩
- issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
- 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
- リポジトリをフォークし、ブランチを切って変更します。
- issue 番号を参照したプルリクエストを送ります。
調査の方向性
まず issue に記載されたデータベース設定を再現し、SMO/SSMS db_datareader ロールメンバーのビューと sys.database_role_members を比較します。Object Explorer の出力に表示される SQL とエントリポイント DBRoleProp を確認します。クエリ結果と一貫してネストされたロールメンバーシップが表示されれば完了です。
索引モデルが issue の本文から書いたものです。
評価
- 技術スタック
- csharp, sql
- 領域
- database
- issue の種類
- バグ
- 難易度
- 4/5
- 見積もり時間
- 3〜5日
- 活発さ
- 停滞
- 明瞭さ
- おおむね明確
- 初心者へのやさしさ
- 35/100