SQL Server Performance Forum – Threads Archive
Delete Guest, ok ?
Would deleting the guest account from MSDB and distributon DB’s do any unforseen harm ? One reason I ask is because I don’t want anyone with a local login to be able to create a DTS pkg. There probably is a better way though.
I am not aware of this causing a problem. The only way a local user logging on can access SQL Server is if they are a local administrator of the server and the built-in admin group is still on your SQL Server, or if they are a SA. Otherwise, they won’t be able to log on and create a DTS package. By the way, you should always remove the built-in admin group from your SQL Server in order to prevent non-SAs (but local admins) from accessing SQL Server. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
I don’t foresee any issues in removing the guest user from MSDB as suggested by Brad if you need them on DTS point of view if you must use the guest account, grant minimum permissions. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Brad,
what I meant by local login was actually a sql server login (probably sounded better as I was typing it), because even if a user has access to the production DB’s they have guest logins to the system DB’s. I’m thinking I should lock this down to be a little tighter, any suggestions ?
satya, I don’t want to use the guest account at all. if I add the user to the system databases as well and add them to db_denyread/writers role that will disallow usage of the guest account since they have a true account on those DB’s, correct ?
Thanks all,
g.
Yes and use with min. permissions. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
If your security is set correctly, average users can’t create or run DTS packages. By default, guest access has no permissions on user databases, unless you give them some. The guess account does exist in system databases, but this does not give them permission to create or execute DTS packages. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Thanks for the posts, For greater clarity and to sum it up… Is it generally better to: 1. Add the user to the system databases master and msdb as a member of the db roles db_denydatareader & db_denydatawriter or 2. Don’t add them to the system databases and let them use the builtin guest account
Hi: "–Take away all access from the public role." do you mean to Deny or Revoke?,
currently the public role in my sys databases has no checkmarks, no X’s "Delete the guest account. Why do you need it?" You cannot delete the guest account in the Master db because without it nobody can logon.
(there is no guest account in the user db’s)
I’ve obviously been smoking crack or something today. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
"By default the public role has access to all kinds of interesting things in the system databases" OK glad you mentioned this, this is exactly what I need to know
the "interesting things" someone might try to use to pull some [email protected] with the DB. All the user has is a SQL Server login and access to the user DB (db_reader/db_writer),
in the system databases only dbo(sa) and guest exist. As I mentioned earlier the public role in the sys databases has no checkmarks and no X’s
but users can open Access, connect to the master DB and execute stored procs (not good) General Suggestions / Comments ?
Suggestions on something I should definately DENY Public access to ?
Have a look at the lockdown script at sqlsecurity.com:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=25 Be sure to go through the options first to see if they fit your environment. When it comes to removing access for public in some areas I use the ones in that script: ———————————– –Remove the pubs and northwind sample databases since they represent known targets with minimal
–permissions for potential attackers.
USE master
DROP DATABASE northwind
DROP DATABASE pubs
GO
—
–Tighten permissions on jobs procedures in case the SQL Agent service is ever activated to prevent low
–privilege users from submitting or managing jobs.
USE msdb
REVOKE execute on sp_add_job to public
REVOKE execute on sp_add_jobstep to public
REVOKE execute on sp_add_jobserver to public
REVOKE execute on sp_start_job to public
GO
—
–Tighten permissions on web tasks table to keep malicious users from creating or altering tasks.
USE msdb
REVOKE update on mswebtasks to public
REVOKE insert on mswebtasks to public
GO
—
–Tighten permissions on DTS package connection table so that malicious users cannot affect DTS packages.
USE msdb
REVOKE select on RTblDBMProps to public
REVOKE update on RTblDBMProps to public
REVOKE insert on RTblDBMProps to public
REVOKE delete on RTblDBMProps to public
GO
—
–Tighten permissions on extended procedures that require heavy use but should not be allowed public access.
USE master
REVOKE execute on sp_runwebtask to public
REVOKE execute on sp_readwebtask to public
REVOKE execute on sp_MSSetServerProperties to public
REVOKE execute on sp_MScopyscriptfile to public
REVOKE execute on sp_MSsetalertinfo to public
REVOKE execute on xp_regread to public
REVOKE execute on xp_instance_regread to public
GO
—
–Revoke guest access to msdb in order to keep any non system administrators from accessing the database without explicit permissions.
USE msdb
EXECUTE sp_revokedbaccess guest
GO
————————
Hi: I’ve seen that script but it doesn’t lock down too many sp’s for example, does the guest user (public role) need EXEC on: sp_addgroup
sp_addlogin
sp_configure
sp_helppullsubscription
xp_unc_to_drive
sp_MS******
ps. Don’t take the above list too literally, it’s just an example of some of the procs that by default a member of the public group (guest) can execute on the Master DB.
A lot of the SPs require the user to be sysamin. sp_addlogin is one example and sp_configure with any change options is another. You need to check each indiviudal SP if there are some you don’t want users be able to run.
Just my opinion, but the most important ones to me are xp_cmdshell and any that deal with permissions. It doesn’t matter what you take away if they still have access to the ones necessary to add a full-scale administrator right back into your system. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>