We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL 2005 grant run job permission to user

Medium Priority
1,056 Views
Last Modified: 2012-05-06
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
I would like to keep the database in windows authentication if possible.
CERTIFIED EXPERT

Commented:
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.

Author

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.
CERTIFIED EXPERT

Commented:
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.

Author

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.
CERTIFIED EXPERT
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.  

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
SQL Server Architect
CERTIFIED EXPERT
Commented:
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.

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.