Words: 806
Time to read: ~ 4 minutes
Update: 2020-07-15 – Thank you Garry Bargsley for being an unofficial editor 🙂
Update: 2020-07-17 – Thanks to Shawn Melton for spot-checking this and letting me know ALL permissions needed!
TL;DR:
All Users:
A user on the database withALTER ANY USER
permission.
Current User and System Users:
To work against all databases for the current user and system users requiresCONNECT ANY DATABASE
.
Update
2020-07-17
Thanks to Shawn Melton for pointing out that CONNECT ANY DATABASE
allows the user to see only themselves and the system users.
To see all users from Get-DbaDBUser
, the caller will need a user on the databases and the permissions ALTER ANY USER
.
CONNECT ANY USER
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
Group-Object -Property Database

4 records are returned – the user itself and the system users.
User & ALTER ANY USER
USE __DBA;
GO
CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO
GRANT ALTER ANY USER TO LimitedPermissions;
GO
The LimitedPermissions
login now has a user in the database and we’ve granted that user the ALTER ANY USER
permission.
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
Group-Object -Property Database

Now, we can see all the users; the user itself, the system users, and the other user I created on the database.
Original Article
The Backstory
Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.
Where am I going with this?
Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.
My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser
to get the users in a database and compare the list against the users we were supposed to create.
If there are any users that should have been created but don’t show up, well then we have a problem.
The Principle of Least Privilege
Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.
I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.
Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.
So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.
But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser
. Even more surprised when I failed and realised I’d have to find out myself.
If anyone else can Google/Bing it and get the answer, please let me know 😐
The Test
Let’s create a new user with no permissions in SQL Server.

USE [master];
GO
CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO
Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?

<#
$Cred
-----
Username = LimitedPermissions
Password = 'MorePermissionsMoreProblems!'
#>
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException
It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException
switch!
The Investigation
It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\
psdrive.
Get-ChildItem -Path Function:\Get-DbaDbUser |
Select-Object -ExpandProperty Definition

See those $server.databases
and $db.users
? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.
The Will is going
To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.
USE __DBA;
GO
CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO

And now let’s try our Get-DbaDbUser
command again.
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
Select-Object Database, Name, LoginType, UserType

Double-Checking
Let’s try all of the databases on the instance now
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
Group-Object -Property Database

Apart, from the system databases (excluding model) it only works on __DBA.
Give it all
Now, let’s use the CONNECT ANY DATABASE
server permission.
USE [master];
GO
GRANT CONNECT ANY DATABASE TO LimitedPermissions;
GO
And we’ll run against all databases again.
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
Group-Object -Property Database

Sin é
That’s it! Minimum permissions that I could find for Get-DbaDbUser
is the permission to connect to the database.
Hope that helps!
One thought on “Minimum Permissions for Get-DbaDbUser”