9 Logins and users
After finding your SQL Server instances in chapter 6, creating an inventory in chapter 7, and adding them to a Registered Server or Central Management Server in chapter 8, you are now ready to deal with users and logins. Ensuring that our business users and applications can successfully connect to the databases that they require is a good way to address issues before they happen. This reduces the time a DBA has to spend resolving issues after they cause problems, or prevent them altogether.
In this chapter, we are going to show how you can simplify the work that is required to administer instance logins and database users by following some common DBA stories around logins. This chapter will take a bit of a different path, because it’s told as a story. We thought that for logins, seeing real-world scenarios would be the most effective way to teach this topic. And by following along with these scenarios, you will learn how to do the following:
That’s a lot to learn, so let’s get started with our first scenario!
9.1 Failed logins
In this story, an application owner reports that they cannot connect to the database, and the login failure is obscured by the connecting application. They report that the application returns “Login failed” to the user, and the application logs have an entry that reads “Can’t connect to the database.”
In this case, we want to find more detailed information about the generic error coming from the application. Although we have a number of ways to do this using tools such as Event Viewer or SQL Server Management Studio (SSMS), we created a command that helps find information quickly in the SQL error log: Get-DbaErrorLog. In the next code listing, we report only the errors that occurred within the past 5 minutes, when the login failure occurred.
Listing 9.1 Getting error log entries for the last 5 minutes
PS> $splatGetErrorLog = @{ SqlInstance = "SQL01" After = (Get-Date).AddMinutes(-5) } PS> Get-DbaErrorLog @splatGetErrorLog | Select LogDate, Source, Text 8/20/2020 11:10:03 PM Logon Error: 18456, Severity: 14, State: 5. 8/20/2020 11:10:03 PM Logon Login failed for user 'Factory'. Reason... 8/20/2020 11:11:05 PM Logon Error: 18456, Severity: 14, State: 5. 8/20/2020 11:11:05 PM Logon Login failed for user 'Factory'. Reason... 8/20/2020 11:11:25 PM Logon Error: 18456, Severity: 14, State: 5. 8/20/2020 11:11:25 PM Logon Login failed for user 'Factory'. Reason...
The results in listing 9.1 show a number of login failures for “Factory,” indicating that the user has not been added. You can easily add a new user with the New-DbaLogin command, which supports both SQL logins and Windows logins. New-DbaLogin also automatically detects what type of user is being added.
SQL logins will prompt for a password, whereas Windows logins will not. This is helpful because, with SQL logins, passwords are required because they are managed within SQL Server. Windows logins are managed outside of SQL Server by Windows or Active Directory, and no password is required during the creation of the new login.
You can see the password prompt in action in the following code snippet where a SQL login is being added, whereas listing 9.3 adds a Windows login, which does not prompt for a password.
Listing 9.2 Creating a new SQL login
# If you use Windows PowerShell, you will receive a credential pop-up # If you use PowerShell Core, you'll be prompted at the console PS> New-DbaLogin -SqlInstance SQL01 -Login Factory Enter a new password for the SQL Server login(s): ********** ① ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : Factory LoginType : SqlLogin CreateDate : 8/20/2020 11:15:03 LastLogin : HasAccess : True IsLocked : False IsDisabled : False MustChangePassword : False
① Prompts for a password for the new SQL login
New-DbaLogin especially shines when you have to add a new login to every server in your estate, such as an auditing group. In the next listing, we use Get-DbaRegisteredServer together with New-DbaLogin to do just that.
Listing 9.3 Adding Windows logins to multiple servers at once
PS> $servers = Get-DbaRegisteredServer PS> New-DbaLogin -SqlInstance $servers -Login adfactoryauditors ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : basefactoryauditors LoginType : WindowsUser CreateDate : 8/22/2020 11:17:03 LastLogin : HasAccess : True IsLocked : IsDisabled : False MustChangePassword : False ComputerName : SQL02 InstanceName : MSSQLSERVER SqlInstance : SQL02 Name : basefactoryauditors LoginType : WindowsUser CreateDate : 8/22/2020 11:17:03 LastLogin : HasAccess : True IsLocked : IsDisabled : False MustChangePassword : False ComputerName : SQL03 InstanceName : MSSQLSERVER SqlInstance : SQL03 Name : basefactoryauditors LoginType : WindowsUser CreateDate : 8/22/2020 11:17:04 LastLogin : HasAccess : True IsLocked : IsDisabled : False MustChangePassword : False
You can even add multiple SQL logins at once without having to type the password multiple times by using Get-Credential, as shown next.
Listing 9.4 Adding SQL logins to multiple servers at once
PS> $servers = Get-DbaRegisteredServer PS> $cred = Get-Credential factoryuser1 ① PS> $splatNewLogin = @{ SqlInstance = $servers Login = $cred.UserName SecurePassword = $cred.Password } PS> New-DbaLogin @splatNewLogin ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : factoryuser1 LoginType : SqlLogin CreateDate : 8/20/2020 11:20:07 LastLogin : HasAccess : True IsLocked : False IsDisabled : False MustChangePassword : False ComputerName : SQL02 InstanceName : MSSQLSERVER SqlInstance : SQL02 Name : factoryuser1 LoginType : SqlLogin CreateDate : 8/20/2020 11:20:07 LastLogin : HasAccess : True IsLocked : False IsDisabled : False MustChangePassword : False
① The credential has been saved in the $cred variable.
Continuing with our story, you have created the login, but the application owner reports that they still cannot log in, even with the correct username and password. Returning to the error log; this time you see the following message: “Login failed for user ‘Factory’. Reason: Failed to open the explicitly specified database ‘WideWorldImporters.’”
We can check whether the database user exists in the WideWorldImporters database with Get-DbaDbUser, as demonstrated here.
Listing 9.5 Checking whether the user exists
PS> Get-DbaDbUser -SqlInstance SQL01 ➥ -Database WideWorldImporters | Select Name Name --- dbo guest INFORMATION_SCHEMA Factory ① sys
As you can see, the user exists in the database ①, which suggests that the database user has been orphaned. Orphaned users exist when a database is moved to another instance and that user does not have a login in the new instance. Orphans can also occur when an availability group fails over to a server where the login has not yet been created.
You can also get orphaned users if you create a login for a database user, but the Security Identifier (SID) is different. This is exactly what we did with our first attempt at resolving the error. How do we know that we have an orphaned user? This is confirmed in the error log shown in the code sample where we connect the instance and return all orphaned users with the Get-DbaDbOrphanUser command.
Listing 9.6 Checking whether orphaned users exist on the instance
PS> Get-DbaDbOrphanUser -SqlInstance SQL01 ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 DatabaseName : WideWorldImporters ① User : Factory ②
The results show that the user Factory ① is an orphaned user in the database WideWorldImporters ②. This matches the errors that you are seeing in the error log.
Before dbatools, resolving this problem was usually a painful process that had to be looked up every time. Now we can just remember the predictable name of the command that repairs orphaned database users, Repair-DbaDbOrphanUser.
In the following listing, we’ll repair all orphaned users in all databases. We do this by not specifying any additional parameters, such as -Database.
Listing 9.7 Repairing the orphaned user
PS> Repair-DbaDbOrphanUser -SqlInstance SQL01 ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 DatabaseName : WideWorldImporters User : Factory Status : Success ①
After running the Repair-DbaDbOrphanUser command, the issue has now been resolved ①, and the application owner can log in successfully.
Let’s summarize the process we just went through, because you can follow this same process whenever you encounter failed logins that you suspect are due to orphaned users, such as when a migration was just performed. Orphaned users are commonly created after databases with SQL users are migrated between servers.
When a user reports an error in an application related to a database login failure, the first step is to check the SQL error log for login failures with Get-DbaErrorLog. Then check the logins and users with Get-DbaLogin and Get-DbaDbUser to look for a matching username. In our case, the user isn’t orphaned because it doesn’t even exist! So we create a new login with New-DbaLogin, but the application is still failing. The next step would be to check for orphaned users. Identify orphaned users with Get-DbaDbOrphanUser, and then, as we did here, repair these orphaned users with Repair-DbaDbOrphanUser.
9.2 Preventing login issues
Even better than fixing a problem is preventing it from happening in the first place. You can take a number of mitigating steps to prevent missing logins or orphaned users. First, you can add documentation to ensure that when a new replica is added, all users are added correctly. You can also use Infrastructure as Code (IaC) to consistently deploy all aspects of the availability group or, in SQL Server 2019 and later, you can use contained databases in availability groups (sqlps.io/containeddbag).
Although we love documentation, there’s no guarantee that the person performing the migration will read it. And IaC is ideal but not always possible due to limitations of current business processes.
We often use a different method to ensure that all of the users are synced across all of the replicas. This method, which schedules an Agent job to frequently sync the users onto each of the replicas, is particularly useful when you aren’t using SQL Server 2019 and have applications that can create users.
The following script ensures that every replica has all of the users. Keep in mind, it doesn’t remove any users from any instance, though; it only ensures that they exist and contain the correct properties, such as password or SID.
Listing 9.8 Syncing users across an availability group
PS> try { # Since this is running as an Agent job, use $ENV:ComputerName # to get the hostname of the server the job runs on $splatGetAgReplica = @{ SqlInstance = $ENV:ComputerName EnableException = $true } $replicas = (Get-DbaAgReplica $splatGetAgReplica).Name } catch { # Ensure SQL Agent shows a failed job Write-Error -Message $_ -ErrorAction Stop } foreach ($replica in $replicas) { Write-Output "For this replica $replica" $replicastocopy = $replicas | Where-Object { $_ -ne $replica } foreach ($replicatocopy in $replicastocopy) { Write-Output "We will copy logins from $replica to $replicatocopy" $splatCopyLogin = @{ Source = $replica Destination = $replicatocopy ExcludeSystemLogins = $true EnableException = $true } try { $output = Copy-DbaLogin @splatCopyLogin } catch { $error[0..5] | Format-List -Force | Out-String # Ensure SQL Agent shows a failed job Write-Error -Message $_ -ErrorAction Stop } if ($output.Status -contains 'Failed') { $error[0..5] | Format-List -Force | Out-String # Ensure SQL Agent shows a failed job Write-Error -Message "At least one login failed. ➥ See log for details." -ErrorAction Stop } } }
By creating a scheduled SQL Agent job to run this script on one of the replicas, you are creating a system that will automatically update itself without requiring input from the DBA. If a new login or user is created by the application, it will be synced across all of the replicas. And if a new replica is added to the cluster, it will have all of the users synced to it.
Now that we understand more about managing logins and users with dbatools, we can move on to a method of tracking any changes that have been made to the users, logins, and permissions on our instances.
9.3 Logins, users, and permissions source control
When dealing with an incident, how many times have you heard a user say, “It used to work before” or “It worked yesterday”? dbatools can help provide you with a list of the users, logins, and permissions from yesterday with Export-DbaLogin, which you can use for differential comparisons, because it exports users, logins, and permissions to disk. Export-DbaLogin is also ideal for nightly login backups and has even saved us a number of times after a login was inadvertently dropped by an authorized user.
This command creates a file that you can use on a different instance to recreate the logins and users, if the databases exist on the instance. When you need to export this sort of detail, you have a lot to consider. For instance, you often need more than just the username and password. You’ll also need properties such as the default database, default language, password expiration, server permissions, database permissions, and more. Export-DbaLogin exports all of this for you, in SQL format, right to disk, as can be seen in the next code listing.
Nightly file exports will enable you to source control your logins and to see what has changed and when it was changed. We suggest that you automate and source control the code to create your logins.
Listing 9.9 Exporting logins on an instance
PS> Export-DbaLogin -SqlInstance SQL01 WARNING: [23:57:23][Export-DbaLogin] Skipping ##MS_PolicyEventProc... ③ WARNING: [23:57:23][Export-DbaLogin] Skipping ##MS_PolicyTsqlExecu... WARNING: [23:57:23][Export-DbaLogin] NT ServiceMSSQLSERVER is ski... WARNING: [23:57:23][Export-DbaLogin] NT SERVICESQLSERVERAGENT is ... WARNING: [23:57:23][Export-DbaLogin] NT SERVICESQLTELEMETRY is sk... WARNING: [23:57:23][Export-DbaLogin] NT SERVICESQLWriter is skipp... WARNING: [23:57:23][Export-DbaLogin] NT SERVICEWinmgmt is skipped... WARNING: [23:57:23][Export-DbaLogin] Skipping disabledsa Directory: C:UserssqldbaDocumentsDbatoolsExport ② Mode LastWriteTime Length Name ---- ------------- ------ ---- -a---- 8/20/2020 11:57 PM 522 SQL01-2020082023... ①
At marker ①, you can see that a file named Instance-Date-login.sql is created in a DbaTools Export directory in the Documents folder ② for the user running the command. The yellow warnings are advising you that the local accounts that cannot be replicated on another machine are being ignored ③. If you open this file, you can see that it contains the T-SQL to recreate the logins and users on the instance, as can be seen in figure 9.1. Notice that not only are the logins created, but they’re also added to the appropriate server roles.
Source control enables you to track and manage changes to code or, more specifically, flat files. If you have installed Git from sqlps.io/installgit, you can initialize a Git repository in a directory, as shown here.
Listing 9.10 Initializing a Git repository
Scheduling this PowerShell code to run every night will source control your logins on the machine where the code is run, as illustrated in the following code snippet. This is a good starting place and does not require you to use any authentication to a remote repository. You can view it as a time machine for your logins.
Listing 9.11 Exporting logins daily
PS> $date = Get-Date PS> $path = "$homesourcerepoSqlPermission" PS> $file = "Factory.sql" PS> Export-DbaLogin -SqlInstance SQL01 -Path $path -FilePath $file PS> Set-Location $path PS> git add $file PS> git commit -m "The Factory users update for $date"
Now you can track how your logins have changed. You can use source control as a time machine to see what the permissions looked like on a particular day.
If your users encounter new login issues, you can use your source control folder to compare the differences in the script between the time it worked and the time it was reported as broken, as depicted in figure 9.2. We recommend using Visual Studio Code.
Click on the source control icon (the one with the number 6 in figure 9.2), and open the source control viewer. In figure 9.2, you can see that the previous night’s commit shows that the db_datawriter permissions have been removed. You know this because they are highlighted in red. With this knowledge, you can investigate the reason for the permissions being removed, but you also have the T-SQL to recreate the logins and permissions as they were yesterday. This means that, if required and approved, you can reset the permissions to the time when “It worked yesterday” because you have the T-SQL scripts available for the point in time that the Export-DbaLogin script was run.
9.4 How was access gained?
You identify that a previous change was responsible for the permission change that removed the db_datawriter permissions and was performed by a user called Brett Miller. Your manager asks you how Brett was able to perform this change. You can gather further details using Get-DbaUserPermission, as shown in listing 9.12.
Get-DbaUserPermission provides a detailed audit of permissions, both at the instance level and the database level, which helps us understand which permissions are in use and perhaps identify unexpected missing or excessive permissions. Ideally, the output of Get-DbaUserPermission will help us find specific permissions that allowed Brett to make unexpected changes.
Listing 9.12 Getting the permissions on the instance
PS> Get-DbaUserPermission -SqlInstance SQL01 -Database WideWorldImporters | Select SqlInstance, Object, Type, Member, RoleSecurableClass | Format-Table SqlInstance Object Type Member RoleSecur... ----------- ------ ---- ------ ---------... SQL01 SERVER SERVER LOGINS dbachecks sysadmin SQL01 SERVER SERVER LOGINS Factory None SQL01 SERVER SERVER LOGINS adFactoryProcesss None SQL01 SERVER SERVER LOGINS adFactoryAdmins None SQL01 SERVER SERVER LOGINS adsqlsvc sysadmin SQL01 SERVER SERVER LOGINS addbateam sysadmin SQL01 SERVER SERVER SECURABLES sqldba SERVER SQL01 SERVER SERVER SECURABLES dbachecks SERVER SQL01 SERVER SERVER SECURABLES Factory SERVER SQL01 SERVER SERVER SECURABLES adFactoryProcesss SERVER SQL01 SERVER SERVER SECURABLES adFactoryAdmins SERVER SQL01 SERVER SERVER SECURABLES adsqlsvc SERVER SQL01 SERVER SERVER SECURABLES addbateam SERVER SQL01 WideWorl.. DB ROLE MEMBERS dbo db_owner SQL01 WideWorl.. DB ROLE MEMBERS Factory db_datare... SQL01 WideWorl.. DB ROLE MEMBERS Factory db_datawr... SQL01 WideWorl.. DB ROLE MEMBERS adFactoryProcesss db_datare... SQL01 WideWorl.. DB ROLE MEMBERS adFactoryProcesss db_datawr... SQL01 WideWorl.. DB ROLE MEMBERS adFactoryAdmins db_owner
This output is useful, but can be a bit overwhelming in the PowerShell console. Using an application such as Excel can help make it easier to see what’s going on. The ImportExcel module, which you learned about in chapter 5, is a fantastic module that is able to perform many tasks with Excel documents.
To take it a step further, you can take your exports to the next level by using the ImportExcel module to color-code output, which makes it easier to visualize permissions. You can use ImportExport to colorize the rows by server login, server-level permissions, database role members, and database-level permissions. You can also use it to help highlight the sysadmin and db_owner members because they will have additional permissions. The code to do this is shown next.
Listing 9.13 Getting the permissions on the instance into Excel
PS> $splatExportExcel = @{ Path = "C: empFactoryPermissions.xlsx" ① WorksheetName = "User Permissions" AutoSize = $true FreezeTopRow = $true AutoFilter = $true PassThru = $true ② } PS> $excel = Get-DbaUserPermission -SqlInstance SQL01 ➥ -Database WideWorldImporters | Export-Excel @splatExportExcel PS> $rulesparam = @{ Address = $excel.Workbook.Worksheets["User Permissions"].Dimension.Address WorkSheet = $excel.Workbook.Worksheets["User Permissions"] RuleType = "Expression" } PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("sysadmin",$G1)))' ➥ -BackgroundColor Yellow -StopIfTrue PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("db_owner",$G1)))' ➥ -BackgroundColor Yellow -StopIfTrue PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("SERVER LOGINS",$E1)))' ➥ -BackgroundColor PaleGreen PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("SERVER SECURABLES",$E1)))' ➥ -BackgroundColor PowderBlue PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("DB ROLE MEMBERS",$E1)))' ➥ -BackgroundColor GoldenRod PS> Add-ConditionalFormatting @rulesparam ➥ -ConditionValue 'NOT(ISERROR(FIND("DB SECURABLES",$E1)))' ➥ -BackgroundColor BurlyWood PS> Close-ExcelPackage $excel ③
Let’s go through listing 9.13 step by step.
First, we specify the destination filename ①, FactoryPermissions.xlsx. Although the file’s extension is .xlsx, Microsoft Excel does not need to be installed on the machine running this code. Exporting (but not viewing) is all taken care of by PowerShell.
After getting the user permissions with Get-DbaUserPermission for the WideWorldImporters database, the results are then piped to Export-Excel using the -PassThru parameter, and saved in the variable $excel ②. Next, conditional formatting is added, depending on the values in the E and G columns. Finally, the Excel package is closed ③. This process gives you an Excel file that looks like the one shown in figure 9.3.
This file enables you to see the permissions in one page, including logins on the server, server-level permissions that have been granted to the logins, the members of each database role, members of the server-level sysadmin role, database-level db_owner role, and database permissions granted to each database user.
Your manager is pleased with the Excel file, but it does not answer their question about how Brett got access. You work closely with the Factory Admins group and confirm with them that they do not have a team member called Brett Miller. So, how did he gain access?
9.4.1 Finding nested Active Directory group access
In a corporate environment, you will find that users are members of Active Directory groups that are members of groups, that are members of groups that are given access to securables. You need to understand how Brett was able to make a change to your database user permissions, but his specific user account was not found within the output of Get-DbaUserPermission. This likely means he is a part of a group that has access to the SQL Server, and that specific group was granted the permissions we’re looking for.
The dbatools command Find-DbaLoginInGroup, shown in the next listing, can help us figure this out. This command accesses Active Directory and works on Linux, as long as your Linux workstation is configured appropriately (which is out of scope for this book).
Listing 9.14 Determining how Brett gained access
PS> Find-DbaLoginInGroup -SqlInstance SQL01 -Login "admiller" SqlInstance : SQL01 Login : admiller DisplayName : Brett Miller MemberOf : adDevOps ParentADGroupLogin : adFactoryAdmins
Running this command shows that Brett is a member of the DevOps Active Directory group, and the DevOps group is a member of the FactoryAdmins group, which has been granted rights to access the SQL Server instance and the database. That explains how Brett got access to make the change: via the nested group permissions.
This same approach can be used each time you need to quickly figure out how a database user was able to make a particular change to a system. You can even schedule these two commands to run each night to get an ongoing, auditable trail of permissions.
Note Although we have learned here that we can create a new user quickly at the command line during incident resolution, we can also use this command to ensure consistent user account creation, for example, when creating nonproduction user accounts.
In this chapter, you have learned several dbatools commands that you can use to administer SQL Server logins and users. You have seen a solution that uses ImportExcel to create an Excel file that less technical users find useful. In our experience, you’ll have many opportunities within your daily work to apply this knowledge to other requirements, using the processes we discussed, such as validating user permissions following a database deployment.
9.5 Hands-on lab
With this lab, you’ll reinforce the following concepts you’ve learned in this chapter:
-
Read the error log on your local instance to familiarize yourself with the output.
-
Set up a local Git repository, and save the output of
Export-DbaLogin. -
Get all of the user permissions on your instance, and export them to an Excel spreadsheet.