Why do I get: 'Login failed for user' when I run a stored procedure?

Question

I can use SQL Server Explorer to copy over schema, data, stored procedures, and functions over to my new dev database. However when I run the stored procedure, I get a 'Login failed for user' error. I can login with the SQL user name and password but I can't run any of the stored procedures?

Answer 1

If you are referencing any remote servers (ie. databases on a different server), then you must make sure that the SQL user has access to those servers.

To check and fix this, in SSMS go to Server Objects > Linked Servers. If you don't have remote server listed there, then you'll have to add it. Also, you'll have to give a username and password that is accessible and has the proper permissions on both servers.