April 3, 2008 at 8:23 am
I am fully aware that you can't run:
REVOKE CONNECT TO GUEST
T-SQL statement against the master and tempdb, as this will raise a 15182 error message.
My question is, how can you stop anybody from querying the system tables in the master database?
Thanks in advance.
Chris.
April 3, 2008 at 8:52 am
Remove the guest user, don't give them access to master.
April 4, 2008 at 5:05 am
Steve Jones - Editor (4/3/2008)
Remove the guest user, don't give them access to master.
Sorry Steve,
that's not an option.
First of all SQL 2005 do't allow you to remove guest user from master. But more important every user must be able to read certain system tables in the master database. For example you need to query sysdatabases in order to determine if a database with a certain name exists on the server.
Or to validate a sql login, the password must be matched to the one stored in syslogins.
Also some system stored procedures are executed when a user logs on to the server. In my experience it's best to leave the permissions as they are. If not you can get unexpected problems. Just think about all the system procedures which are executed when you open Object Browser in SSMS.
[font="Verdana"]Markus Bohse[/font]
April 9, 2008 at 10:27 pm
MarkusB (4/4/2008)
Steve Jones - Editor (4/3/2008)
Remove the guest user, don't give them access to master.Sorry Steve,
that's not an option.
First of all SQL 2005 do't allow you to remove guest user from master. But more important every user must be able to read certain system tables in the master database. For example you need to query sysdatabases in order to determine if a database with a certain name exists on the server.
Or to validate a sql login, the password must be matched to the one stored in syslogins.
Also some system stored procedures are executed when a user logs on to the server. In my experience it's best to leave the permissions as they are. If not you can get unexpected problems. Just think about all the system procedures which are executed when you open Object Browser in SSMS.
This depends on the version of SQL Server. In SQL Server 2000 you can reduce the surface area to just sysdatabases and spt_values, if I remember right. SQL Server 2005 there shouldn't be a need to do this. While permission is granted to the system views, what the user can actually see is rather limited.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply