May 24, 2011 at 6:29 am
I have a few questions about jobs and stored procedures in SQL server:
1) Jobs are run via the SA, right? If so, then couldn't a person who has permissions to edit a job, go into a job, write any SQL syntax they want (e.g. DROP DATABASE...) and then have the job run that command, despite they themselves not having DROP DATABASE permissions?
2) Also, what's the advantage/disadvantage to having sa be the owner of a job? If a person leaves the organization, then you don't want their account tied to the job/database/stored procedure, etc., right? I've made sa the owner of all of the databases here (does that make sense?). I'm contemplating getting everything consistent and doing the same with the jobs.
3) If a person or role (with that person in it) has execute permissions on a stored procedure, must he/she have the necessary permissions to the tables/views referenced within that stored procedure to be able to have it run successfully, or does it work like a view, where you only need permission to the view and none of the underlying tables?
I know I could set up a bunch of test cases/users and run through all of these, but I was hoping that someone knew the answers off the top of their head, so I don't have to take as much time to find out the answers.
Thanks for any help.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 24, 2011 at 6:42 am
Hi Mike,
mikes84 (5/24/2011)
1) Jobs are run via the SA, right?
Nope. Jobs are run under the account of the job owner. Steps are run as the job owner if it's not a sysadmin, otherwise they are run as the SQLAgent service account. If there's a proxy account, the step can be set to run as the proxy account, that can be coupled with a credential.
A bit tricky, I have to admit.
If so, then couldn't a person who has permissions to edit a job, go into a job, write any SQL syntax they want (e.g. DROP DATABASE...) and then have the job run that command, despite they themselves not having DROP DATABASE permissions?
In order to run a job, a user must be granted at least SQLAgentUserRole. To start a job owned by a different user, the SQLAgentOperatorRole is needed.
2) Also, what's the advantage/disadvantage to having sa be the owner of a job? If a person leaves the organization, then you don't want their account tied to the job/database/stored procedure, etc., right? I've made sa the owner of all of the databases here (does that make sense?). I'm contemplating getting everything consistent and doing the same with the jobs.
I usually assign job ownership to a special login for this purpose.
3) If a person or role (with that person in it) has execute permissions on a stored procedure, must he/she have the necessary permissions to the tables/views referenced within that stored procedure to be able to have it run successfully, or does it work like a view, where you only need permission to the view and none of the underlying tables?
You just need the execute permissions on the procedure.
I know I could set up a bunch of test cases/users and run through all of these, but I was hoping that someone knew the answers off the top of their head, so I don't have to take as much time to find out the answers.
It's a good idea. I recommend testing it all. You could also find out that my answers are dead wrong, who knows?
Hope this helps,
Gianluca
-- Gianluca Sartori
May 24, 2011 at 6:48 am
Gianluca, thanks for the responses.
If you only need Execute permissions on the stored procedure to run any code within it, then what's to stop someone who has execute and edit permissions on the stored procedure from editing it, adding "DROP DATABASE <database>" and then executing it? I guess this is a similar question to my one about the jobs and permissions.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 24, 2011 at 7:02 am
"To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database."
Do not give your users CONTROL permissions on the database.
Disable the sa account, don't use it.
May 24, 2011 at 7:04 am
Well, EXECUTE permissions does not imply ALTER permissions.
Moreover, to drop the database you must be the database owner have CONTROL permissions.
SQLServer security model is fairly granular and it's usually quite easy to assign the right permission set to the users.
Edited: corrected, thanks to Calvo.
-- Gianluca Sartori
May 24, 2011 at 7:44 am
Disable the sa account
Why disable it? What if all users are accidentally locked out or deleted. SA is your guaranteed way in, no?
don't use it.
Don't use it for the owner of databases/jobs or don't use it period? Should a special login be used in its place?
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 24, 2011 at 9:04 am
mikes84 (5/24/2011)
Disable the sa account
Why disable it? What if all users are accidentally locked out or deleted. SA is your guaranteed way in, no?
don't use it.
Don't use it for the owner of databases/jobs or don't use it period? Should a special login be used in its place?
Mike
Agreed. Disable the account and don't use it.
If you are locked out, there's a lot of methods to gain access other than using sa.
-- Gianluca Sartori
May 24, 2011 at 9:21 am
It's a best practice to not use it. It's got way too much power and is a *known* sysadmin login account which makes it super easy to brute force your way in. If you absolutely need sysadmin privs, create a different account (sql or windows authentication) and grant sysadmin to it. This way no one knows which account is the sysadmin and you can control who is using it (windows group).
As for database and job owner, perhaps a service account to handle those? (minimum privileges) This way you're not relying on users to own database objects. I'm sure there are many suggestions how to handle these, just have to look for them.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply