SQL Server Error - "Cannot execute as the database principal because the principal [name]" does not exist"

Question

I've searched for an answer to this, but nothing I've tried so far has worked. So what happened was my DBA created a copy of a SQL Server database for me. I have several SQL Server Reporting Services (SSRS) reports in a project (in my Visual Studio solution) and they all must connect to the new database, via pre-defined credentials.

The credentials in question consist of a username and a password. If the the database name is "DbName" and the username in question is "dbUserName", the error I get is this:

Cannot open database 'DbName' requested by login. The login failed. Login failed for user 'dbUserName'.

When looking at the properties for database "DbName" in SQL Server Management Studio (SSMS), under the "Permissions" menu, I do see an entry for 'dbUserName' as a User. I see it's been granted a Connect permission, but when I click on tab "Effective", I get the following error:

Cannot execute as the database principal because the principal "dbUserName" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Also, one more piece of information that might be useful. If, in SSMS, I go in the Security folder for database "DbName" and then expand Users, I do see user 'dbUserName' there. Looking at its properties reveals its user type as a "SQL user without login", while in a different database (basically a test copy of 'DbName') the user type is "SQL user with login".

Any idea what's causing the errors in question?

Answer 1

When you see "SQL user without login" as the "User type", that indicates the database contains a user by that name, but the SQL Server instance does not have a login linked to that user.

You can rectify that using the sp_change_users_login statement, as detailed in @rvsc48's answer, or you can use the ALTER USER syntax:

ALTER USER <db_account_name> WITH LOGIN = <instance_account_name>;

If the account does not exist at the instance level, you can create it using the CREATE LOGIN syntax, as in:

CREATE LOGIN <instance_account_name> 
WITH PASSWORD = 'P@ssw0rd';

Once the login is created, run the ALTER USER command above to link the two together.

Answer 2

It sounds like the login did not get mapped to the database user following the restore. Try running EXEC sp_change_users_login 'Update_One','dbUserName','dbUserName' pointing to the database and then see if you still receive the error. For reference - sp_change_users_login

Answer 3

Recently I had experienced this issue.

Try to find whether your login is a Group or User. If its a group you cannot use Exec AS Login = N'domain\group name'; as it cannot impersonate the users belong to that group to validate.

If it's a group, get members of the group using the following command EXEC xp_logininfo N'domain\group name','members';.

Once you get the list of members then you can use:

EXEC as LOGIN = N'domain\member';
GO
SELECT SUSER_SNAME();
GO
SELECT TOP (1) * FROM dbo.tablename;
GO
REVERT; 
GO
SELECT SUSER_SNAME();

Hope this solution works.