March 24, 2010 at 2:39 pm
I just realized that SQLAgentReaderRole can create, modify and drop its own jobs on a server!
I had given some users access to view job info through membership to this role, but I now realize this is too much in terms of permissions.
This creates a gaping security hole on any server on which these permissions are granted.
How is one to give users access to view job info but not create their own jobs on the server?
I must say, I find the name of this role totally misleading...
Am I missing something?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 25, 2010 at 4:19 pm
The "reader" name is definitely misleading. This may be stating the obvious, but to get around this I've granted the SQLAgentReaderRole role and explicitly denied EXECUTE on add job sprocs in msdb for the logins with the role.
DENY EXECUTE ON [dbo].[sp_add_job] TO [YourLogin]
GO
DENY EXECUTE ON [dbo].[sp_add_jobschedule] TO [YourLogin]
GO
DENY EXECUTE ON [dbo].[sp_add_jobserver] TO [YourLogin]
GO
DENY EXECUTE ON [dbo].[sp_add_jobstep] TO [YourLogin]
GO
DENY EXECUTE ON [dbo].[sp_add_jobstep_internal] TO [YourLogin]
GO
March 26, 2010 at 7:29 am
It doesn't matter that much because they would be the owner o the job, and could not do anything inside the job for which they do not have permissions. The SQL engine would check whether the owner of the job had permissions to the subsystem such as SSIS or cmdline.
In effect there is no security loop hole at all because all that is being granted is the permission to schedule something they could already do anyway.
Regards,
Toby
March 26, 2010 at 7:34 am
Toby White (3/26/2010)
It doesn't matter that much because they would be the owner o the job, and could not do anything inside the job for which they do not have permissions. The SQL engine would check whether the owner of the job had permissions to the subsystem such as SSIS or cmdline.In effect there is no security loop hole at all because all that is being granted is the permission to schedule something they could already do anyway.
Regards,
Toby
The owner of any job can drop, create, modify any database objects in the SQL instance (including databases themselves), since job is running under the SQL Agent service account.
It is an enormous security hole.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2010 at 7:51 am
You are making an incorrect assumption. You are correct that the job runs under a different context than the owner of the job. What you are failing to realize is that the owner of the job must have permissions to use the subsystem before the Agent Service will perform the action. The subsystems being:
ActiveX Script
Operating System
Replication XXX
SSAS
SSIS
ETC
I am guessing the reason you are unaware of this is because you don't use proxies and all the job steps run under the agent service which is typical, so you never thought about it before. Anyway, if you don't believe me I can deliver an article, just let me know.
Regards,
Toby
March 26, 2010 at 8:01 am
Marios Philippopoulos (3/26/2010)
The owner of any job can drop, create, modify any database objects in the SQL instance (including databases themselves), since job is running under the SQL Agent service account.It is an enormous security hole.
I would not call that a security hole. Create a proxy account as Toby suggested or don't give them the permission unless they accept the cost.
I just realized that SQLAgentReaderRole can create, modify and drop its own jobs on a server!
This again is by design. MSDN states that SQL Server Agent database role permissions are concentric in relation to one another.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 26, 2010 at 8:13 am
One further note - for the step type T-SQL the step is actually executed under the context of the owner. You can test this out easily enough by creating a job with one step with a non-ddl owner and have the step create a test table. You will get the error back:
Executed as user: DomainName\UserName. CREATE TABLE permission denied in database 'DBM'. [SQLSTATE 42000] (Error 262). The step failed.
Regards,
Toby
March 26, 2010 at 9:59 am
Toby White (3/26/2010)
One further note - for the step type T-SQL the step is actually executed under the context of the owner. You can test this out easily enough by creating a job with one step with a non-ddl owner and have the step create a test table. You will get the error back:Executed as user: DomainName\UserName. CREATE TABLE permission denied in database 'DBM'. [SQLSTATE 42000] (Error 262). The step failed.
Regards,
Toby
Thanks both, Toby I understand what you are saying now;
yes, the reason I did not realize this earlier is because I have never used proxies to control SQL Agent access.
I will look into this further as I am only superficially familiar with using proxies.
I still think though that the way MS set up permissions for this role is very misleading. Just as there is a db_datareader role for database access, there should be a built-in SQLAgent role with read-only access to jobs.
Am I being unreasonable?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 11, 2014 at 9:08 am
No, not unreasonable. I am just exploring this and was surprised
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply