I am working on creating a app based on (.net) which will be using Windows Athentication for Connecting to SQL Server.
Use of App:
Can you Please suggest the Best way to create and impliment the user Security.
Thanks & regards, Sudhir Nune
I don't think you will be able to get around that bottleneck. With regards to using sp____start____job you will want to read over the security requirements for execute permissions on it. It involves using the [SQL Server Agent fixed database roles]. However executing a job is only available if the user owns the job according to documentation.
I believe to do your restrictions you will need to filter this on the application side. They may be able to execute any job but you only return the job_id for ones you want them to see and execute on. However I see a big overhead keeping that up over time.: http://msdn.microsoft.com/en-us/library/ms188283.aspx
answered Jan 23, 2012 at 10:46 AM
By default normal user doesn't have access to the
However you can use
For getting details about job steps you can use
Access to the jobs is controlled by
If user isn't member of any above mentioned groups, the not jobs will be listed.Generally you do not need to take care about the secrutiy as SQL Server only allows operation allowed by permissions granted in above mentioned groups. It means if user is member of SQLAgentUserRole and is not owner of the job, sql server doesn't allow to execute particular job and will raise an error.