Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dowens
Yak Posting Veteran
82 Posts |
 Posted - 2005-09-07 : 10:53:59
|
| I have an external user that uses VPN to connect to my SQL server.He has permissions to views only. User also has a SQL server.Last night this user used a wizard to create a DTS job that created and droped several tables. Also created a Agent to execute the job.The job failed because the user did not have Admin permissions.This morning the user went in and deleted the Agent job.How is this possible?What can I do to prevent this in the future? |
|
|
MuadDBA
628 Posts |
Posted - 2005-09-07 : 11:06:04
|
| I beleive that, by default, you can create SQL Server jobs if you are in the PUBLIC role, however, the jobs won't have any more permission than the account with which they were created (unless they set them up to run as another account, but if they can do that, you're security is already compromised).IF you want to restrict them from doing this, you can remove them from the PUBLIC role and add them as db_Datareader. I think this should do the trick. |
 |
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-09-07 : 11:20:45
|
| Thanks I'll try that! |
|
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-09-07 : 11:53:30
|
| I also went to msdb and removed the public permissions for the spsp_add_dtspackage. |
|
|
|
MuadDBA
628 Posts |
Posted - 2005-09-07 : 12:56:56
|
| Why do that? Again, remove them from the public role in that database, and they should no longer be able to execute that stored procedure. Otherwise, now you may have to specifically enable that procedure for any other user you might want to be able to create DTS jobs. |
|
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-09-07 : 14:54:41
|
| How do I remove a user from Public.I've tried and it keeps giving me 'Members cannot be dropped from Public'. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-08 : 01:59:34
|
| just revoke all permissions from public, this will restrict accounts that are members of the public role--------------------keeping it simple... |
|
|
|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-09-08 : 09:12:40
|
| Thanks that worked! |
|
|
|
|
|
|
Security Issue