February 6, 2008 at 6:28 pm
I am trying to secure our new 2005 server and am curious if remove the following from public will cause any problems.
These are all default setting that I am looking to lock down. I have been trying to find information on what these tables and stored procedures actually are used for but I am pretty new to this. If it makes any difference this is setup as a failover cluster and will be used to host our MOSS 2007 database.
master public SELECT spt_fallback_db
master public SELECT spt_fallback_dev
master public SELECT spt_fallback_usg
master public SELECT spt_monitor
master public SELECT spt_values
msdb public SELECT restorefilegroup
msdb public SELECT logmarkhistory
msdb public SELECT suspect_pages
msdb public EXECUTE sp_get_dtsversion
msdb public EXECUTE sp_make_dtspackagename
msdb public EXECUTE sp_add_dtspackage
msdb public EXECUTE sp_drop_dtspackage
msdb public EXECUTE sp_reassign_dtspackageowner
msdb public EXECUTE sp_get_dtspackage
msdb public EXECUTE sp_enum_dtspackages
msdb public EXECUTE sp_log_dtspackage_begin
msdb public EXECUTE sp_log_dtspackage_end
msdb public EXECUTE sp_log_dtsstep_begin
msdb public EXECUTE sp_log_dtsstep_end
msdb public EXECUTE sp_log_dtstask
msdb public EXECUTE sp_enum_dtspackagelog
msdb public EXECUTE sp_enum_dtssteplog
msdb public EXECUTE sp_enum_dtstasklog
msdb public EXECUTE sp_dump_dtslog_all
msdb public EXECUTE sp_dump_dtspackagelog
msdb public EXECUTE sp_dump_dtssteplog
msdb public EXECUTE sp_dump_dtstasklog
msdb public SELECT backupmediaset
msdb public SELECT backupmediafamily
msdb public SELECT backupset
msdb public SELECT backupfile
msdb public SELECT restorehistory
msdb public SELECT restorefile
Thanks for the help.
Jeremy
February 6, 2008 at 7:56 pm
Most of these are system stored procedures.
First, don't allow guest or others into msdb, and you can ignore the public permissions there. If a user doesn't have access to the database, it doesn't matter if public has rights.
For the master ones, I think those are used for clustering. Don't let mess with those as you could cause issues.
If you have all your logins mapped to users in the MOSS database, and that's there default, then this shouldn't be an issue.
February 7, 2008 at 9:04 pm
Thanks for the information. I had previously removed the guest account from access to the msdb. So I assume that the public that is granted to those sp's in the msdb is the msdb public role. Does that public role tie into global public role of the instance? If then even though if I remove guest access, wouldn't users that have accounts in the instance still have public access to the sp's. Luckily with MOSS we plan to only create service accounts in the database so access should be limited to admin and service accounts, I am just trying to think ahead for other SQL deployments.
February 8, 2008 at 6:45 am
There is no global public role. There's only a public role in each database and you can't remove that role. I wouldn't think any system functions relied on that, but I wouldn't mess with the permissions for public (removing permissions) in system databases.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply