SSMS <-> SMO Bug listing db_datareader role members
Nadie ha tomado este issue todavía.
Evaluación
- Dificultad
- 4/5
- Tiempo estimado
- 3-5 días
- Aptitud para principiantes
- 35/100
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.
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:
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
- Lenguaje dominante
- C#
- Estrellas
- 143
- Forks
- 28
- Métricas de merge de PR
- Sin PR fusionados en 30 d
Preparar el entorno
Aún no hemos revisado los archivos de configuración de este proyecto. Empieza por su README y consulta nuestra guía para la primera contribución para los pasos generales.
Primeros pasos
- Lee el issue completo y luego la guía de contribución del proyecto.
- Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
- Haz un fork del repositorio y trabaja en una rama.
- Abre un pull request que haga referencia al número del issue.
Más de microsoft/sqlmanagementobjects
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 62/100
-
Dificultad 3/5 1-2 días Aptitud para principiantes 72/100
-
Dificultad 4/5 3-5 días Aptitud para principiantes 74/100
-
Dificultad 3/5 1-2 días Aptitud para principiantes 78/100
-
Dificultad 3/5 1-2 días Aptitud para principiantes 68/100
Todos los issues de microsoft/sqlmanagementobjects
Issues similares
-
area-System.Numerics.Tensors untriaged
Dificultad 2/5 1-3 horas Aptitud para principiantes 78/100
dotnet/runtime#134691 · 2 comentarios ·
Los mantenedores suelen responder en 1 día
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 72/100
unoplatform/uno.templates#2277 ·
Los mantenedores suelen responder en 5 días
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 78/100
microsoft/fluentui-blazor#5344 · 1 comentario ·
Los mantenedores suelen responder en 1 día
-
difficulty/starter 🚀 good first issue kind/bug platform/all project/core-tools 🛠️ triage/untriaged
Dificultad 2/5 1-3 horas Aptitud para principiantes 88/100
unoplatform/uno#24757 ·
Los mantenedores suelen responder en 1 día
-
ci-failure-cause test-failure
Dificultad 2/5 1-3 horas Aptitud para principiantes 72/100
Los mantenedores suelen responder en 1 día