How to find logins without user mapping in a specified SQL Server database (PowerShell)

Introduction

This PowerShell script will demo how to find all the logins without user mapping in a specified SQL Server database. Besides, it will list all the users and logins, except for server roles and server principals with names enclosed by double hash marks ?#?which are for internal system use only.

Scenarios

Some people may encounter an error "The login already has an account under a different user name" when creating a user account for a specified database. So they need to create a user with another login. If there are many logins, it is not easy for them to try one by one. It will be better if there is a script to find all the available logins.

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator

2. Run the script in the form: & Path
For example: & "E:\OneScript\Julie\GetLoginsWithoutUsers.ps1"

3. Press "Enter" and enter the instance name and database name.

4. Press "Enter" choose the authentication, then you will get the result as below:

Here are some code snippets for your reference:

try{  
                #Open database
                $SQLConnection.Open()
                             
                #SQL query statement to get all the logins without users
                $SQLQuery = " select sp.name AS LoginName,sp.type
                            from sys.server_principals sp
                            left join sys.database_principals dp
                            on sp.sid= dp.sid
                            where sp.type  <> 'R' AND sp.name NOT LIKE '##%' AND dp.name IS NULL"
                            
                #Create SqlDataAdapter object with command text and connection
                $SQLDataTable = New-Object System.Data.DataTable
                $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection)
                $SQLAdapter.Fill($sqlDataTable) | Out-Null
                
                #Output all the logins without users
                $n = $SQLDataTable.Rows.Count
                Write-Host "There are $n logins without users! " 
                $SQLDataTable
            }

try{   
                #Open database 
                $SQLConnection.Open() 
                              
                #SQL query statement to get all the logins without users 
                $SQLQuery = " select sp.name AS LoginName,sp.type 
                            from sys.server_principals sp 
                            left join sys.database_principals dp 
                            on sp.sid= dp.sid 
                            where sp.type  <> 'R' AND sp.name NOT LIKE '##%' AND dp.name IS NULL" 
                             
                #Create SqlDataAdapter object with command text and connection 
                $SQLDataTable = New-Object System.Data.DataTable 
                $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection) 
                $SQLAdapter.Fill($sqlDataTable) | Out-Null 
                 
                #Output all the logins without users 
                $n = $SQLDataTable.Rows.Count 
                Write-Host "There are $n logins without users! "  
                $SQLDataTable 
            }

Prerequisites

SQL Server 2005 /SQL Server 2008/SQL Server 2008 R2/SQL Server 2012

Windows PowerShell 2.0, Windows PowerShell 3.0 or Windows PowerShell 4.0

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.