4

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?

2

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.

  • What if this is happening while using the sa or a domain admin account? Those shouldn't need to be linked to specific databases within the instance, should they? – Dan Csharpster Nov 21 '17 at 18:54
  • sa (the SQL Server sa account, not a generic system administrator account) does not need explicit access, it already has access to everything in SQL Server. A domain admin account will not necessarily have access at all to SQL Server. – Max Vernon Nov 21 '17 at 19:30
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.