SQL Server Error – How to Fix ‘Cannot Execute as the Database Principal’ Issue

sql serverssrs

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?

Best Answer

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.

Related Question