dbatools
dbatools copied to clipboard
Export-DbaUser includes unrelated database roles in export
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
- 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
- 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