microsoft / microsoft/sqlmanagementobjects

SSMS <-> SMO Bug listing db_datareader role members

Abierto
#44 7 comentarios 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Lenguaje dominante
C#
Estrellas
143
Forks
28
Métricas de merge de PR
Sin PR fusionados en 30 d

Descripción

@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

Guía de contribución

No hay ninguna guía de contribución indexada para este repositorio

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Línea de trabajo

Comienza reproduciendo la configuración de la base de datos del issue y compara la vista de miembros del rol db_datareader de SMO/SSMS con sys.database_role_members. Revisa el SQL mostrado en la salida de Object Explorer y el punto de entrada DBRoleProp; se considera terminado cuando las membresías de roles anidados se muestran de forma coherente con los resultados de la consulta.

Escrito por el modelo de indexación a partir del texto del issue.

Evaluación

Stack tecnológico
csharp, sql
Área
database
Tipo de issue
Error
Dificultad
4/5
Tiempo estimado
3-5 días
Estado de actividad
Estancado
Claridad
Bastante claro
Aptitud para principiantes
35/100

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.