Check login&database user | The server principal “xxxxxxxxxxxxxx” is not able to access the database “xxxxxxxxxxxx” under the current security context.

IT

Introduction

The following error occurred when attempting to connect to SQL Server recently.

The server principal “xxxxxxxxxx” is not able to access the database “xxxxxxxxxx” under the current security context.

This error means that a user is unable to connect to the database.

In many cases, the login has not been created or the database user may not be associated with the login properly.

To connect to SQL Server (Azure SQL Database, Azure Synapse Analytics), you need a login and a database user associated with that login.

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that isn’t based on a Windows principal (such as an SQL Server login).

Reference:Create a Login – SQL Server | Microsoft Docs

This article therefore explains how to check the details of login and database users.

How to check

In conclusion, the following query can be used to check the login&database user.

Login

select * from sys.sql_logins

Reference:sys.sql_logins (Transact-SQL) – SQL Server | Microsoft Docs

Database user

select * from sys.database_principals

Reference:sys.server_principals (Transact-SQL) – SQL Server | Microsoft Docs

Detailed procedure

1. Log in to the server in SQL Server Management Studio (SSMS)

First of all, follow the steps below to connect from SSMS to SQL Server.

Reference:Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Docs 

2. Right-click on ‘master’ and select New Query. Execute the following query.

Logins are created in the master database, so you need to make a query against master.

The following query will be used to retrieve the login information.

Query

select * from sys.sql_logins

 3. Click Export results and save in CSV format.

In SSMS, query results can be exported by right-clicking and selecting ‘Save Results As…..’ to export the output results.

4. Right-click on the user database and select New Query. Execute the following query.

The following query will retrieve information about users in the user database.

Query

select * from sys.database_principals

5. Click Export results and save in CSV format.

The query results are exported in the same way as in step 3.

6.Check login and database user status from exported CSV files.

Check the login and user status from the CSV file obtained.

Possible situations are

.

  • Logins have not been created in the first place
  • The login has been created, but the user has not been created
  • Logins and users with the same name exist, but with different SIDs.

The connection may have failed for one of the above reasons.

 

The main aspects to check include

  • Whether a login exists in ‘sys.sql_logins’
  • Whether the target user exists in ‘sys.database_principals’.
  • Whether a login with the same SID as the target user exists (if the login name is the same but the SID is different, the user is considered different).

The problem may be fixed by recreating the login or user according to the following.

Create a user using T-SQL
In Object Explorer, connect to an instance of Database Engine.

On the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute.

— Creates the login AbolrousHazem with password ‘340$Uuxwp7Mcxo7Khy’.
CREATE LOGIN AbolrousHazem
WITH PASSWORD = ‘340$Uuxwp7Mcxo7Khy’;
GO

— Creates a database user for the login created above.
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;
GO
For more information, see CREATE USER (Transact-SQL) which contains many more Transact-SQL examples.

Reference:Create a Database User – SQL Server | Microsoft Docs

Summary

In this article, I have shown you how to check the login and database user details.

To connect to SQL Server (Azure SQL Database, Azure Synapse Analytics), you need a login and a database user associated with the login.

If you cannot connect with the following error, check the status of the login and database user.

The server principal “xxxxxxxxxx” is not able to access the database “xxxxxxxxxx” under the current security context.

The confirmation procedure is as follows.

the following query can be used to check the login&database user.

Login

select * from sys.sql_logins

Reference:sys.sql_logins (Transact-SQL) – SQL Server | Microsoft Docs

Database user

select * from sys.database_principals

Reference:sys.server_principals (Transact-SQL) – SQL Server | Microsoft Docs

参考

sys.sql_logins (Transact-SQL) – SQL Server | Microsoft Docs

sys.server_principals (Transact-SQL) – SQL Server | Microsoft Docs

Create a Database User – SQL Server | Microsoft Docs