T-SQL to Find Database Role Permissions

To find out what permissions a database role was granted, I use this query.

USE DatabaseName
GO
 
SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
--WHERE o.name = 'RoleName'
--    AND s.name = 'dbo';

Granting Sendmail to Login in SQL Server

Granting sendmail to a login/user is a common request that we DBAs get from developers.  They usually need to send reports via email on their stored procedures.

Here are the steps to how I grant them this privilege.

First, create a database role in the msdb database.

 

USE [msdb]
GO
CREATE ROLE [DatabaseMailUserRole]
GO

Then grant permissions to the new role.

USE [msdb]

GO

GRANT SELECT ON dbo.sysmail_allitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_sentitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_unsentitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_faileditems TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sysmail_delete_mailitems_sp TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_mailattachments TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_event_log TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sysmail_help_status_sp TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sp_send_dbmail TO [DatabaseMailUserRole];

Finally, grant this role to the login.

USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [LoginID]
GO

  

Granting Read-Only Access to an AD Group to an Azure SQL Database

This morning I got a request to grant read-only database access to an AD Group.  Here's how I did it:

Using SSMS, I ran these commands both in the master and the user database specified on the request.  The reason why I added the user to the master database without any permissions is to allow login to succeed without defining a default database on the connection string.

Here's the code:

In the master database:

CREATE USER [AD Group Name] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]

GO

In the User database:

CREATE USER [AD Group Name] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember 'db_datareader', [AD Group Name]
GO

Simple AD Search with Powershell Get-ADUser

 I needed to do a quick search in AD for a user this morning.  I wrote this script, hope it helps someone.

Clear-Host

Clear-History

$ADFilter = Read-Host -prompt "Search AD "

Get-ADUser -Filter {(Surname -like $ADFilter) -or (GivenName -like $ADFilter)} -Properties *

    | Select-Object DisplayName, samAccountName, EmailAddress

    | Format-Table -AutoSize

Getting the AD Groups a User Belongs In To Grant Database Access

We usually get requests that ask for database permissions that you pattern with another user's permissions.  This is not the ideal scenario.  What should happen, is they need to give what type of permissions they need or they should provide the AD groups they need to be added to.  But from years of working as a DBA, we still get requests of this nature.

So, annoyed this morning, I wrote a PowerShell script to grab all the AD groups a login belongs to, compare it to the AD groups defined in the database, and return the intersection.   

Here's the script, I hope it helps someone.

Clear-Host

Clear-History

$userID = Read-Host -prompt "Username "

$serverName = Read-Host -prompt "Server Name "

$databaseName = Read-Host -prompt "Database Name "

$UserGroups = Get-ADPrincipalGroupMembership -Identity $UserID | Select-Object -ExpandProperty Name

$buffer1 = $UserGroups -join "', '"

$GroupsString = "'$buffer1'"

if ($serverName -Match ".database.windows.net") {

    $strQueryAD = "SELECT name AS [Group Name] FROM sys.database_principals WHERE type_desc IN ('EXTERNAL_GROUP') AND REPLACE(name,'$env:USERDOMAIN\','') IN ($GroupsString)"

    Connect-AzAccount -WarningAction Ignore | Out-Null

    $access_token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token

    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -AccessToken $access_token -Query $strQueryAD -Verbose

}

else {

    $strQueryAD = "SELECT name AS [Group Name] FROM sys.database_principals WHERE type_desc IN ('WINDOWS_GROUP') AND REPLACE(name,'$env:USERDOMAIN\','') IN ($GroupsString)"

    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -Query $strQueryAD -TrustServerCertificate -Verbose  

}

Changing Multiple AD Passwords Using Powershell

I wrote this script a while back to change my password on multiple domains.

Make sure that all your passwords are synced between domains.   If the script fails, update your passwords manually, you don't want your accounts getting locked.

During execution, your passwords will be shown on the screen in clear text, so make sure no one is looking.

Here's the code:

$userName = "loginID"

$oldPassInput = Read-Host "Please enter your old password"

$oldPassword = ConvertTo-SecureString -AsPlainText $oldPassInput -Force

$newPassInput = Read-Host "Please enter your new password"

$newPassword = ConvertTo-SecureString -AsPlainText $newPassInput -Force

$domainArray = @("domain1", "domain2", "domain3", "domain4", "domain5")

## $domainArray = @("mpi")

foreach ($domain in $domainArray)

{

    # $User = $userName

    $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "$domain\$userName", $oldPassword

    try {

        Get-Aduser $userName -Credential $Credential -Server $domain

        Write-Output "Login successful.  Old password is good on $domain."

        try {

            Set-ADAccountPassword -Credential $Credential -Server $domain -Identity $userName -OldPassword $oldPassword -NewPassword $newPassword

            Write-Output "Password changed on $domain"

        }

        catch {

            Write-Output "Error changing password on $domain. This could happen if your passwords are not synced, or expired, or new password doesn't meet security policies."

        }

    }

    catch {

        Write-Output "Login Failed.  Incorrect old password on $domain."

    }

}

Generate Pass Phrases with Powershell

I have written a PowerShell script to generate passphrases.  

It uses two files to get words from (5-LetterNouns.txt and 5-LetterAdjectives.txt).  You can get these files from Git Hub.  There are a few of them out there. 

I like using real words more than that mixed character-symbol-number stuff.  It makes me remember things easier.

Here is the code.

function Get-RandomWord {

    $lines = Get-Content -Path "$PSScriptroot\5-LetterNouns.txt"

    $words = $lines -split '`n'  # Split content into words

    $randomWord = Get-Random -InputObject $words

    return $randomWord

}

function Get-RandomAdjective {

    $lines = Get-Content -Path "$PSScriptroot\5-LetterAdjectives.txt"

    $adjectives = $lines -split ' '  # Split content into words

    $randomAdjective = Get-Random -InputObject $adjectives

    return $randomAdjective

}

function Generate_PassPhrase {

    $selectedAdjective1 = Get-RandomAdjective

    $selectedAdjective1 = (Get-Culture).TextInfo.ToTitleCase($selectedAdjective1.ToLower())

    $selectedWord = Get-RandomWord

    $someNumber = Get-Random -Minimum 0 -Maximum 9

    $Generate_PassPhrase = "$SelectedAdjective1 $selectedWord $someNumber"

    return $Generate_PassPhrase

}

for ($i = 1; $i -le 10; $i++) {

    $NewPassPhrase = Generate_PassPhrase

    Write-Output $NewPassPhrase

}

Here is a sample output:

Lanky dance 3

Grand world 2

Right silly 1

Joint earth 5

Tired tower 3

Black shake 1

Worse split 0

Glass bonus 5

Bumpy other 1

Brown block 8

The script generates 10 passphrases at a time, you can change the for loop if you need more choices.

Unlocking a Login in Sybase ASE

I was working on an incident this morning, my friend who is a part of the IT governance team cannot access her account.  The application uses Sybase ASE as its database.

I asked for her login credentials.  She gave me her ID, but she wasn't sure which instance she needed.  I told her that was fine, I only needed to check 2 instances for her based on what she was trying to do.

I logged in to the database instances in question and did a sp_displaylogin for her ID.  I saw that in one of the instances, her ID didn't exist, and it was locked in the other instance.  I figured that she would want access to the instance she already had an account in.  

Now that I know what instance to fix her login in, I ran a sp_locklogin to unlock her account.

sp_locklogin userID, 'unlock'
go

Easy resolution.

T-SQL to Change UTC Time to Central Time

If you have been working with Azure SQL Server, you would have queried the sys.event_log table to check the logs of the server.  I needed to query this table to investigate the issue of an application failing to wake up the database in a paused state.  

Azure SQL Servers tend to be in UTC time format when you create them by default.

If you run a SELECT GETDATE(), it will return a UTC time following what the server is on.

My timezone is Central Standard Time.  I needed to convert UTC to CST.

Here's the code I used to do that:

SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

And back to the sys.event_log, here's the code I used, converting the [start_time] and [end_time] columns.

SELECT TOP (1000) [database_name]
      ,[start_time] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS [start_time]
      ,[end_time] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS [end_time]
      ,[event_category]
      ,[event_type]
      ,[event_subtype]
      ,[event_subtype_desc]
      ,[severity]
      ,[event_count]
      ,[description]
      ,[additional_data]
  FROM [sys].[event_log]
  ORDER BY [start_time] DESC
 

Changing Multiple UNIX Passwords Using Powershell

When you work in a company as an administrator you would often find yourself changing passwords regularly.  Through the years it gets tiresome to change passwords multiple times across multiple hosts.  I try to keep my passwords the same which is not best practice, but it helps me remember them better.  Plus, I will change the passwords again in a month anyway.  With added, VPN/firewall in place, I think it is not a big deal to keep your admin passwords the same for the month.

I have written a PowerShell script to change my UNIX password for this purpose.  It requires the Posh-SSH Powershell module that you can get from the PSGallery.

To install the module run this command in your Powershell terminal:

Install-Module -Name Posh-SSH -Scope AllUsers

This script was tested and worked on Powershell 7.4.1.  

Please note that when you run the script it will prompt you for your old and new password.  This will be shown in clear text on the screen.  This is on purpose so you can remember or write down the password for safekeeping.  

For slower connections to the UNIX hosts, you can adjust the Start-Sleep duration.  There are two Start-Sleep in the code, first is for the sleep in between the passwd dialog, and the 2nd is for how fast the host returns a prompt to you after you log in.  Please adjust these two variables based on your environment.

## SCRIPT START ##

Clear-Host

$userName = "yourUNIXid"

$SleepBetweenPasswordDialog = 10

$SleepAfterLogin = 60

$oldPassInput = Read-Host "Please enter your old password"

$oldPassword = ConvertTo-SecureString -AsPlainText $oldPassInput -Force

$newPassInput = Read-Host "Please enter your new password"

$newPassword = ConvertTo-SecureString -AsPlainText $newPassInput -Force

$UNIXCred = New-Object System.Management.Automation.PSCredential -ArgumentList $userName,$oldPassword

$UNIXServerArray = @("unixHost1", "unixHost2")

Write-Output "`n`n"

foreach ($UNIXServer in $UNIXServerArray)

{

    Write-Output "*** Changing password in $UNIXServer ***`n`n"

    $session = New-SSHSession -ComputerName $UNIXServer -Credential $UNIXCred

    $shellStream = New-SSHShellStream -SessionId $session.SessionId

    

    # How fast your UNIX host take you to a prompt after login

    Start-Sleep -Seconds $SleepAfterLogin

    $command = "passwd"

    $shellStream.WriteLine("$command")

    Start-Sleep -Seconds $SleepBetweenPasswordDialog

    #pass in old password

    $shellStream.WriteLine([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($oldPassword)))

    Start-Sleep -Seconds $SleepBetweenPasswordDialog

    #pass in current password

    $shellStream.WriteLine([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($newPassword)))

    Start-Sleep -Seconds $SleepBetweenPasswordDialog

    #pass in current password

    $shellStream.WriteLine([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($newPassword)))

    Start-Sleep -Seconds $SleepBetweenPasswordDialog

    # Clear buffer, should see "password updated" message

    $shellStream.Read()

    $shellStream.Dispose()

    $session | Remove-SSHSession

    Write-Output "`n`n`n"

}

## SCRIPT END ##

Msg 229, Level 14, State 5, Procedure ProcName, Line 1 [Batch Start Line N]

Today, I got a message from a developer that she is getting the following error when trying to execute a store procedure.

Msg 229, Level 14, State 5, Procedure cp_s_proc_dc_incr, Line 1 [Batch Start Line 2]

The EXECUTE permission was denied on the object 'cp_s_proc_dc_incr',  database 'entpr_dat_wrhs', schema 'dbo'...

This error would mean that her account or the AD group that she belongs to doesn't have EXECUTE permission on the procedure in question.

I went into SSMS and expanded the database Security/Users folder.  Here I can see if she has a user for herself or the AD group that she belongs to exists.  

She seems to belong to an AD group.  Her individual AD account isn't there.  But I see her AD group.  I'll use that.

I also went into the Security/Roles/Database Roles folder to see if there is already an execute stored procedure role that we usually create in my DBA team.

Nice, we already have an "exec_procs_role" in there.  So, I will just use that to give her the permission she needs.

GRANT EXECUTE ON [exec_procs_role] TO [AD Group Name];

Easy peasy.  I got her to test and the issue was resolved.