• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

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?
0
talbers_bci
Asked:
talbers_bci
  • 4
  • 4
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
milduraitCommented:
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
 
DBAduck - Ben MillerPrincipal 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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now