February 27, 2008 at 5:44 am
Hi,
I am using SQL2000 std edition, with the help of windows user every body is logging into sqlserver. I am working in adding users and assigning permissions to the user. I have denied the permission for windows user password. After denying i am not able to login to query analyzer. With the dbo rights i created users and i have logged in.
After denying the windows user my sqlserver agent is not executing the scheduled job. It is throwing error as
"SQL Server Scheduled Job 'TRANSACTION BACKUP' - Status: Failed - Invoked on: 2008-02-27 15:40:00 - Message: The job failed. Unable to determine if the owner of job TRANSACTION BACKUP has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs). "
when i drilled down to the error and saw that my sqlserveragent service is using the windows user as logon to which i denied permission earlier.
For running the job i have enabled the windows user and all users are able to login to query analyzer without userid.
Whether anyone how to resolve the issue.
February 28, 2008 at 7:16 pm
This has come across rather confusingly and it's hard to make out exactly what your setup is.
The Windows account used by the service will need sa rights in SQL Server and should be specified as an individual user (or add it to a Windows group that only it is a member of and grant login+sa to that). Any other Windows user needs to have either an individual login created or be added to a domain group and that group granted permissions to login to SQL Server + whatever rights they need within any databases.
The only way that all users could login to the server with Query Analyzer is if they were all logging in with the same account SQL Server is using. Or you have disabled a SQL login that maps to a Windows GROUP, that both the users & the SQL service account are in.
Either way, it isn't entirely clear what you've got setup there.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 28, 2008 at 9:16 pm
Please explain the details of what you did as Scott asked.
I'm not sure the service account needs SA rights, especially the SQLAgent account, but it does need rights to connect to the server and if it's doing backups, at least the backup operator in that database.
There should be no one using the Service accounts to log into Windows. These should be set with long, complicated passwords and be separate accounts from those of any user.
February 28, 2008 at 9:25 pm
Thanks Scott
i have solved the problem by creating a separate windows based user for sqlagent and created a separate user-id for all users with separate rights.
My sqlagent account is specifally used to login to the server and it is restricted to other users .
With this my sqlagent is running without error and users are also able to login thru their respective user-id.
September 30, 2008 at 3:51 am
can you please tell me how you created an account and granted it the sqlagent role
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply