SQL 2005 grant run job permission to user

I am having trouble granting users permission to start a job in SQL 2005.  Currently I have a program, developed in visual basic 2008, that uses sql tables.  These tables need to be updated periodically and sometimes immediately.  I have grated the user builtin\users permission into the msdb so that I can start a job using a stored procedure but it isn't working correctly.  Currently I get an error of "The specified job @job_name('something') doesn't exist.  Now if I run the same procedure from my login, administrator, it works fine.  I know it has to do with permissions on the SQL box but I can't find out where to fix it.  Good or bad the user builtin\users has execute permissions and is a member of targetserversrole along with db_executor, I made this up to grant execute permissions on the msdb.  Where do i go from here?  Or what information do you need to help me?
LVL 1
talbers_bciAsked:
Who is Participating?
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
In order to run a job you would need to make the User in msdb database part of the SQLAgentOperator or SQLAgentUser roles.

They are under the Security in msdb database and Database Roles.
0
 
milduraitCommented:
Do you really need to use Active Directory to control access to the database, or can your application just connect using sql authentication?  To do this the sql server must be setup in mixed mode.
0
 
talbers_bciAuthor Commented:
I would like to keep the database in windows authentication if possible.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
milduraitCommented:
The permissions that you would be looking for are inside sql server 2005.
Do you have SQL Management Studio installed on the server?

Personally, I would just use a generic standard sql login that does everything, and let the coding in my application determine what a user can or can't do in the database, else you are going to be forever having permissions issues.
0
 
talbers_bciAuthor Commented:
SQL Management Studio is installed on the server and I can access it from my machine.   Not a bad solution and I don't have any problems with creating a generic login.  I am sure I will have the same questions on the permissions.  Some thoughts on this would be appreciated.
0
 
milduraitCommented:
I don't have a copy of 2005 running here to give you exact instructions, but will try my best.

You need to make sure you server is in mixed mode (ie accepts both sql and ad logins).  If you right click the server node, you should be able to find and apply the setting.

Next, you should be able expand nodes and traverse down to security, and add a new login (usename and password, making sure it is not using windows authentication)

Each database has a user/security section also so you can map the login to the database, and give it db_owner priveledges in your app database, and also appropriate permission in other / system databse.

Finally modify your app's connection string.

Sorry about the fuzzy instructions, but if you are developing in VS2008 you should be able to find your way around.
0
 
talbers_bciAuthor Commented:
I will use this solution as a last effort type, I just don't want to open up mixed mode unless I have to.
0
 
milduraitConnect With a Mentor Commented:
You could try associating AD Users with a group and then associate the group with a sql login, and then giving that sql login permissions over the databases and objects using the above method.  
0
 
talbers_bciAuthor Commented:
I am awarding mildurait partial credit on the question because that solution will work, I just didnt  want to use mixed mode, and for sticking with the question.  Dbaduck's solution is the fix for me, I changed permissions on the msdb and everything works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.