microsoft / microsoft/sqlmanagementobjects

SSMS <-> SMO Bug listing db_datareader role members

Open
#44 7 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
143
Forks
28
PR merge metrics
No merged PRs in 30d

Description

@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

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the database setup in the issue and compare the SMO/SSMS db_datareader role-members view with sys.database_role_members. Review the SQL shown in the Object Explorer output and the DBRoleProp entry point; done means nested role membership is displayed consistently with the query results.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.