January 26, 2024 at 4:57 pm
I am new here and I hope this is the place to place this question.
When I schedule an Agent Job Activity it fails with the error:
Login failed for user 'LCDOM100\LCISQL##'. [SQLSTATE 28000] (Error 18456) Cannot open database "LCDW" requested by the login. The login failed. [SQLSTATE 42000] (Error 4060). The step failed.
This is a procedure that joins against another Database however, when I run this Procedure using my credentials it runs fine. We have setup the security for that server but not sure why I get the login failed. Any ideas what I can do?
January 26, 2024 at 5:13 pm
Login failed means that the account that it is running as doesn't have access to that database. In the error it lists who it is running as (LCDOM100\LCISQL##) and what database it is trying to access (LCDW). So you will need to give the account access to the database and any objects it needs access to, then it'll succeed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 28, 2024 at 5:48 pm
Verify the Account's Permissions: Double-check that 'LCDOM100\LCISQL##' has the appropriate permissions not just at the database level but also the necessary schema or object-level permissions required for the tasks the job is attempting to perform.
Use SQL Server Management Studio (SSMS): For a more visual approach, use SSMS to navigate to the "Security" section under the "LCDW" database. Right-click on "Users" and ensure 'LCDOM100\LCISQL##' is listed with appropriate roles assigned.
Review the Job's Configuration: Ensure that the job step's "Run as" setting aligns with the account 'LCDOM100\LCISQL##'. Sometimes, a mismatch here could cause unexpected access issues.
Testing in a Controlled Environment: As Mr. Gale wisely points out, always test changes in a non-production environment first to avoid unintended consequences. If possible, replicate the job and its conditions in a test environment to verify the solution before applying it to your production environment.
Consult Documentation: Microsoft's documentation on SQL Server permissions and security models can provide additional insights into best practices and troubleshooting steps for these types of errors.
Understanding the underlying cause of access denials is crucial for not only resolving the current issue but also for reinforcing your database's security posture. Remember, precise control over permissions minimizes potential vulnerabilities while ensuring necessary tasks can be performed without hindrance.
January 29, 2024 at 9:29 am
Verify Account Permissions: Ensure that the 'LCDOM100\LCISQL##' account has been granted access to the "LCDW" database. This includes both login permissions on the SQL Server instance and user mappings inside the "LCDW" database with the necessary roles to execute the tasks.
Adjust Job Settings: Check the job's configuration, particularly the "Run as" setting for the job step, to ensure it aligns with the 'LCDOM100\LCISQL##' account. This ensures the job executes with the correct permissions.
Use SSMS for Verification: Utilize SQL Server Management Studio (SSMS) to navigate to the "Security" section under the "LCDW" database. Confirm that 'LCDOM100\LCISQL##' is listed under "Users" and has appropriate roles assigned.
January 29, 2024 at 4:32 pm
Thank you for all the tips. And just to clarify I am using SSMS to run this job and to monitor the output. The user that is running this job is the SA with all the System Administrator credentials it does have all authority to do everything. That is why I am so confused as to why is failing. The permissions are correct to read and write for that user and I still don't know why I am getting that error. Thanks
January 29, 2024 at 5:18 pm
SQL jobs run as the SQL agent service account. It does NOT run as "sa". That is why you are getting the error. You will notice the error does NOT say that login failed for user 'sa', it says login failed for user 'LCDOM100\LCISQL##'. That is your SQL Agent SERVICE account.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 29, 2024 at 7:28 pm
Thank you all, and Thanks Brian Gale for the explanation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply