Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Export-DbaUser includes unrelated database roles in export #9231

Open
0x7FFFFFFFFFFFFFFF opened this issue Jan 29, 2024 · 0 comments · May be fixed by #9232
Open

Export-DbaUser includes unrelated database roles in export #9231

0x7FFFFFFFFFFFFFFF opened this issue Jan 29, 2024 · 0 comments · May be fixed by #9232
Labels
bugs life triage required New issue that has not been reviewed by maintainers

Comments

@0x7FFFFFFFFFFFFFFF
Copy link
Contributor

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No errors. Just wrong results.

Steps to Reproduce

  1. Execute the following T-SQL commands to set up the database, user, and roles:
USE master;
GO
CREATE DATABASE db1;
GO
CREATE LOGIN user1 WITH PASSWORD = 'StrongPassword!123';
GO
USE db1;
GO
CREATE USER user1 FOR LOGIN user1;
GO
CREATE ROLE role1;
CREATE ROLE role2;
GO
EXEC sp_addrolemember 'role1', 'user1';
GO
  1. Export the user using dbatools' Export-DbaUser command:
Export-DbaUser -SqlInstance node1 -Database db1 -User user1

Expected Behavior

The export should only include the user user1 and any roles or permissions directly associated with user1.

Actual Behavior

The export includes the creation of role2, which has no association with the user user1. Here is the exported script:

USE [db1]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'role1' AND type = 'R')
CREATE ROLE [role1]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'role2' AND type = 'R')
CREATE ROLE [role2]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
CREATE USER [user1] FOR LOGIN [user1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [role1] ADD MEMBER [user1]
GO
GRANT CONNECT TO [user1] AS [dbo];
GO

This issue can lead to unnecessary bloat in the exported script and potentially create security misconfigurations if the script is run without review in different environments. It would be beneficial if Export-DbaUser could improve the logic to limit the export to entities that are explicitly related to the specified user.

Please confirm that you are running the most recent version of dbatools


Major  Minor  Build  Revision
-----  -----  -----  --------
2      1      7      -1

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version


Name                           Value                                                                                   
----                           -----                                                                                   
PSVersion                      5.1.20348.2110                                                                          
PSEdition                      Desktop                                                                                 
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                 
BuildVersion                   10.0.20348.2110                                                                         
CLRVersion                     4.0.30319.42000                                                                         
WSManStackVersion              3.0                                                                                     
PSRemotingProtocolVersion      2.3                                                                                     
SerializationVersion           1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)   Aug  7 2021 01:20:37   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

.NET Framework Version

PSChildName                      Version
-----------                      -------
v2.0.50727                       2.0.50727.4927
v3.0                             3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation  3.0.6920.4902
v3.5                             3.5.30729.4926
Client                           4.8.04161
Full                             4.8.04161
Client                           4.0.0.0
@0x7FFFFFFFFFFFFFFF 0x7FFFFFFFFFFFFFFF added bugs life triage required New issue that has not been reviewed by maintainers labels Jan 29, 2024
@0x7FFFFFFFFFFFFFFF 0x7FFFFFFFFFFFFFFF linked a pull request Jan 29, 2024 that will close this issue
Open
11 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bugs life triage required New issue that has not been reviewed by maintainers
Projects
None yet
Milestone
No milestone
1 participant
@0x7FFFFFFFFFFFFFFF