microsoft / microsoft/sqlmanagementobjects

SSMS <-> SMO Bug listing db_datareader role members

Aperta
#44 7 commenti 0 reazioni 0 assegnatari Vedi su GitHub

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:

image

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

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. 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

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.