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 |
|
vpoko
Starting Member
22 Posts |
 Posted - 2006-01-31 : 13:59:39
|
| I'm setting up SQL Server 2000 and need to secure it before loading data. I have revoked all privelages from the public role in my DB, but am concerned with users being able to look at or make changes to the built-in DB's (master, tempdb, etc). Can I also revoke (not deny) all privelages on all objects in these databases, or does public need a minimum level of access to work (i.e., access to TempDB to use temporary tables). Thanks! |
|
|
vpoko
Starting Member
22 Posts |
Posted - 2006-02-01 : 10:07:44
|
| So it looks like the Public role has a pretty complicated security assignment (individual columns in master tables are granted/denied). This leads me to think I shouldn't touch Public permissions. Doesn't this somehow create a security hole, though? Can't public, by browsing TempDB and Master get important system information and peek at user-space tables? |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2006-02-01 : 12:44:00
|
| There is no real metadata security in SQL2000 and the guest user cannot be dropped from master or tempdb (which is how users with no explicit acccess gain access to these databases). SQL2005 does improve on this situation with a grantable view definition permission which by default normal users do not have except for objects they own or have explicitly been granted e.g. just because a user can run a stored procedure there's no reason for them to be able to view the code. In SQL200 however you're pretty much stuck and I wouldn't advise modifying the default permissions in system databases (assuming you are on SP3a or higher)HTHJasper Smith |
|
|
|
vpoko
Starting Member
22 Posts |
Posted - 2006-02-01 : 12:55:06
|
| This was the answer I was afraid of. How badly does this compromise the server? To me, it seems SQL Server 2000 is not secure at all because of this. Am I overreacting? |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2006-02-01 : 13:10:21
|
It's not something I lose any sleep about  It's not an ideal situation but its not the end of the world either. You can have a look at the SQLSecurity.com Lockdown Script if you're interested in pursuing it but you would need to test this very carefully as it is rather stringent.HTHJasper Smith |
|
|
|
vpoko
Starting Member
22 Posts |
Posted - 2006-02-01 : 13:47:33
|
| Thanks for your help! |
|
|
|
|
|
|
Public role and built-in databases