Non-admin users will have public access to their databases, and usually SQL Server agent won't show in their Management studio. If you want the public user to create or manage or execute the Jobs in the SQL SERVER, you have to add 3 important SQL Agent fixed database roles to the msdb database.
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
1. Navigate through Object Explorer -> Security -> Logins(select the user, then right click) -> Properties -> User Mapping -> Login Properties.
2. Select msdb database.
3. Select SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole under Database role membership for : msdb.
That's it. You are good to go....
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
How to add those roles
1. Navigate through Object Explorer -> Security -> Logins(select the user, then right click) -> Properties -> User Mapping -> Login Properties.
2. Select msdb database.
3. Select SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole under Database role membership for : msdb.
That's it. You are good to go....
No comments:
Post a Comment
Please include your thoughts/suggestion to make it as a better blog. If you find it useful, Please update with your valuable comments, so that others can use it.