SQL: Cannot execute as the database principal because the principal "dbo" does not exist

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Framework Microsoft SqlClient Data Provider):

• Server Name: server_name or IP
• Error Number: 15517
• Severity: 16
• State: 1
• Line Number: 1

Resolution: ALTER AUTHORIZATION ON DATABASE::[YourDatabaseName] TO [sa];

SQL: Linked Server Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”. 

This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

To check if SQL is using Kerberos:

USE [master]
GO
SELECT      s.session_id, s.original_login_name, c.net_transport, c.auth_scheme, c.local_net_address, c.local_tcp_port, s.program_name
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c on s.session_id = c.session_id
WHERE s.is_user_process = 1

To determine the authentication method of a connection, execute the following query:

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Validating Authentication Properties Used by Connections

USE [master]
GO
SELECT COUNT(auth_scheme) as sessions_count, net_transport, auth_scheme
FROM sys.dm_exec_connections
GROUP BY net_transport, auth_scheme

 

To resolve this:

• Both SQL Servers and the Client must be in the same Domain
• Both SQL Servers must be able to register SPNs (Service Principal names)

SQL: Create user and grant read-only access to a database

1. Create the Login (if it doesn’t exist)
If the login for the user hasn’t been created at the SQL Server level, you need to create it. This example assumes the user is an SQL Server authentication user.

    USE [master];
    CREATE LOGIN [new_username] WITH PASSWORD = 'your_password';

2. Grant Access to the Database
Now, switch to the database you want to grant access to and create a user associated with the login.

    USE [your_database_name];
    CREATE USER [new_username] FOR LOGIN [new_username];

3. Add the User to the db_datareader Role
Finally, add the user to the db_datareader role to provide read-only access to the database.

    ALTER ROLE [db_datareader] ADD MEMBER [new_username];