talbers_bci
asked on
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?
mildurait
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.
ASKER
I would like to keep the database in windows authentication if possible.
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.
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.
ASKER
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.
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.
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.
ASKER
I will use this solution as a last effort type, I just don't want to open up mixed mode unless I have to.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.