microsoft / microsoft/sqlmanagementobjects
SSMS <-> SMO Bug listing db_datareader role members
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:

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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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