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 with ALTER ANY USER permission.

Current User and System Users:
To work against all databases for the current user and system users requires CONNECT 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!