The Get-DbaUserPermission Command
I started using dbatools a while ago instead of the commonly known SqlServer PS modules. A regular thing a developer or DBA will need to know is the permissions of users in a database. And dbatools offers two useful commands: Get-DbaUserPermission and Get-DbaPermission. In this blog, I will explain the basics of the former, Get-DbaUserPermission.
What's important to mention here is that I am using the Windows account I logged into my laptop, which has sysadmin access to my local AdventureWorks database. Later in this blog, I try to use a custom Windows account and perform the same steps here with different results and a recommended workaround.
$sql = Connect-DbaInstance -SqlInstance ESPEED4\SQLEXPRESS -Database AdventureWorks -TrustServerCertificate
Once you're connected, we can pass the SQL Instance to the Get-DbaUserPermission command like this:
Get-DbaUserPermission -SqlInstance $sql -Database AdventureWorks | ft
If you get a list with no errors, then your user session has the right permissions on the database to list the database user information. Note that I filtered out the system database information like master and tempdb.
If we throw a -Verbose flag in the above command, we see some interesting logging. Notice the STIG schema that is created and the tempdb usage for the objects in order to build temporary data storage for the command's output:
Testing a Custom Windows Account
However, you may get an error mentioning that you do not have the correct permission to create a schema, and the command fails. To test this, I created a custom Windows account named <local>\TestSqlUser1 and gave it a password. I will use this new test account to compare the results above that was using an admin account from which I had logged in with on my laptop.
Below you will see, what I thought, was a strange error message mentioning a STIG schema. Here is the output when using Get-DbaUserPermission on an account without the proper permissions.
So you might be asking, like me, what is stig.sql for in SQL Server, and why does dbatools create a STIG schema when invoking the Get-DbaUserPermission command? I tried MS Copilot with the responses below.
What is STIG?
Per Copilot
The stig.sql script in SQL Server is part of the Security
Technical Implementation Guide (STIG) provided by the Defense Information
Systems Agency (DISA). STIGs are a set of guidelines to ensure that systems,
including SQL Server, are configured securely and comply with the Department
of Defense (DoD) security requirements[1a][2a]. The stig.sql script helps implement these security controls
by checking and enforcing various security settings and configurations.
When you use the Get-DbaUserPermission module from dbatools, it
creates a STIG schema in the tempdb database. This schema is
used to store temporary objects and results generated by the script as it
gathers detailed permissions information for server and database roles and
securables[3a]. This process is particularly useful for DISA STIG implementors, as it
leverages the Permissions.sql script provided by DISA to ensure
compliance with security guidelines[3a].
So, what user permissions are needed to run the Get-DbaUserPermission module on a database in dbatools? Copilot gives us this answer.
To run the Get-DbaUserPermission module in dbatools, you
need sufficient permissions to access the SQL Server instance and the
databases you want to query. Specifically, you should have:
-
Server-Level Permissions: You need to be a member of the
sysadminfixed server role or have theVIEW SERVER STATEpermission. This allows you to access server-level information and permissions. -
Database-Level Permissions: You should have the
VIEW DATABASE STATEpermission on each database you want to query. This permission allows you to view the state of the database and its objects.
These permissions ensure you can retrieve detailed information about server and database roles, logins, and securables[1b][2b].
Proving and Disproving Copilot's Response
However, the above Copilot response was not entirely true. In #1 above, it says a user or group needs to have sysadmin OR the VIEW DATABASE STATE granted at the database level. And in #2 above, it says you need VIEW SERVER STATE at the server-level permissions. So when I created my Windows account named TestSqlUser1 and gave it both VIEW SERVER STATE at the server level and VIEW DATABASE STATE at the database level, I still could not run Get-DbaUserPermission without getting a permissions error. Also, note that Copilot says nothing of granting the CREATE SCHEMA for a user or group, and that is part of the error message output from Get-DbaUserPermission if the permissions are not satisfied properly. Here's the error output for permissions when Get-DbaUserPermission tries to access and create the STIG schema.
VERBOSE: [00:01:30] [Connect DbaInstance] Starting loop for ‘local\SQLEXPRESS‘ : Computer Name = ‘local’, InstanceName = ‘SQLEXPRESS’, Is Local Host = ‘True’, Type = 'Server1 VERBOSE: [00:01:30] [Connect-DbaInstance] Server object passed in, will do some checks and then return the original object VERBOSE: [00:01:30][Get-DbaUserPermission] Reading stig.sql VERBOSE: [00:01:30][Get-DbaUserPermission] Removing STIG schema if it still exists from previous run VERBOSE: [00:01:30][Get-DbaUserPermission] Creating STIG schema customized for master database WARNING: [00:01:30][Get-DbaUserPermission] Failed to create or use STIG schema on local\SQLEXPRESS | User does not have permission to perform this action. CREATE SCHEMA failed due to previous errors.
Here's proof of the SQL permissions on my local SSMS for my Windows user TestSqlUser1.
C:\>runas /user:Local\TestSqlUser1 "pwsh.exe" Enter the password for Local\TestSqlUser1: Attempting to start pwsh.exe as user "Local\TestSqlUser1" ...
$SqlQuery = @" SELECT dp.name AS UserName, dp.type_desc AS PrincipalType, o.name AS ObjectName, o.type_desc AS ObjectType, p.class_desc AS PermissionClass, p.permission_name AS PermissionName, p.state_desc AS PermissionState FROM sys.database_permissions p LEFT JOIN sys.objects o ON p.major_id = o.object_id INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id WHERE dp.type IN ('S', 'U', 'G') -- S: SQL user, U: Windows user, G: Windows group ORDER BY dp.name, o.name, p.permission_name; "@ $advWorksConnection = Connect-DbaInstance -SqlInstance 'local\SQLEXPRESS' -Database 'AdventureWorks' -TrustServerCertificate Invoke-DbaQuery -SqlInstance $advWorksConnection -Query $SqlQuery | Format-Table -AutoSize
Running the Script from Visual Studio Code
What does this tell us?
Copilot 1 response References