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.
answered Jan 23, 2012 at 10:46 AM
By default normal user doesn't have access to the
However you can use `EXEC dbo.sp_help_job` to get list of jobs. Only jobs to which particular user has access will be listed.
For getting details about job steps you can use `EXEC sp_help_jobstep 'job guid'` to return steps of particular job. Again it only returns rows for job to which user has access and will fail for JOB GUID to which user doesn't has access. But as you receive only list of jobs to which user has access you can request only those steps.
Except `EXEC dbo.sp_help_job` you can use **`select from sysjobs_view`* at least or SQL 2008R2 and again it will return only those jobs to which user has access.
Access to the jobs is controlled by
`[SQLAgentUserRole]` - member of that role can list and execute only jobs for which they are owners. Users are also able to create jobs.
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.