Wednesday, November 27, 2024

Using Get-DbaUserPermission module in dbatools

 

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:

  1. Server-Level Permissions: You need to be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission. This allows you to access server-level information and permissions.

  2. Database-Level Permissions: You should have the VIEW DATABASE STATE permission 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.





Once you obtain the correct server-level and database-level permissions mentioned above, I first open a PowerShell command as the custom Windows User I created earlier, .\TestSqlUser1.  Make sure you are running your command window as an administrator. 

C:\>runas /user:Local\TestSqlUser1 "pwsh.exe"
Enter the password for Local\TestSqlUser1:
Attempting to start pwsh.exe as user "Local\TestSqlUser1" ...

Then, I tried re-running the Get-DbaUserPermission command as the TestSqlUser1, expecting to get a list of user permissions, but that failed again.



So, this is back to where I started, and I did not make progress with the custom Windows User.  The only way I could get it to work was with an account with sysadmin privileges, which I was trying to avoid giving to my custom Windows Account (i.e., TestSqlUser1).  So then I had a thought.  The dbatools is choosing to use a STIG schematic and a temp table in the tempdb database.  I know I can run a SELECT query in SSMS as long as my Windows account has db_datareader.  Maybe I can still use dbatools and run query text and have it return those results?  And I was able to do this from this PowerShell script: 

$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?


Firstly, if you are a DBA, you will most likely have enough privileges to run any dbatools commands and get output for day-to-day needs. If you are running from your local computer and have administrative rights to it, then you can easily give yourself sysadmin or db_owner rights to run these commands without issues. However, I was trying to mimic a non-DBA person looking for a way to use dbatools to get user permissions and their grant details, in this test case, for the TestSqlUser1 account.  

What we found under the hood is that the dbatools Get-DbaUserPermission cmdlet introduces additive steps to create a STIG schema and uses the tempdb database to store the objects needed for the output on display.  So, I had to revert to a native T-SQL query and run it with the dbatools Invoke-DbaQuery cmdlet.  The advantage is that I didn't have to worry about the permissions just to run a query for informational purposes that an application team may want to know for a migration project.  The disadvantage is that you have to know or find out the appropriate tables and join keys along with what columns to display within the SQL Server system tables.  This is not something application teams may be privy to, as a DBA would most probably know. It's great if you are a DBA, but you may find that if you are a MIS Team, some of these commands are off limits, and rightly so, for security reasons.

Copilot 1 response References

at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)