microsoft / microsoft/sqlmanagementobjects
SSMS <-> SMO Bug listing db_datareader role members
Nessuno ha ancora preso questa issue.
- Lingua principale
- C#
- Stelle
- 143
- Fork
- 28
- Metriche di merge delle PR
- Nessuna PR unita negli ultimi 30g
Descrizione
@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
Guida per i contributori
Nessuna guida per i contributori indicizzata per questo repository
Come iniziare
- Leggi tutta la issue e poi la guida ai contributi del progetto.
- Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
- Fai un fork del repository e lavora su un branch.
- Apri una pull request che faccia riferimento al numero della issue.
Direzione di ricerca
Inizia riproducendo la configurazione del database nell'issue e confronta la vista dei membri del ruolo db_datareader di SMO/SSMS con sys.database_role_members. Esamina l'SQL mostrato nell'output di Object Explorer e l'entry point DBRoleProp; il lavoro è completato quando l'appartenenza a ruoli annidati viene visualizzata in modo coerente con i risultati della query.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Valutazione
- Stack tecnologico
- csharp, sql
- Ambito
- database
- Tipo di issue
- Bug
- Difficoltà
- 4/5
- Tempo stimato
- 3-5 giorni
- Stato di attività
- Ferma
- Chiarezza
- Abbastanza chiara
- Idoneità per principianti
- 35/100