SQL Security

[DaveR]

Hello all,
I'm having a bit of trouble securing my SQL Server 2000 database.
I have a windows group set up, and this can view one database only using an application role.

Any other databases I have created , the users cant access and this works fine.
However , the system databases , can be accessed.
eg. the user can enter the 'master' database and view data etc..

In the 'master' database , in the users section , the only users listed are dbo and guest.

I have a feeling its something to do with the Public role , but I aint sure how to correct the problem.

If anybody has any idea , let me know.

cheers

[S@NSIS]

Hi,
Go to the Logins of your users (found under Security > Logins) and then look at their properties, it will tell you which databases they have access to (which you can then change).

S@NSIS

[DaveR]

In the windows group of users , they have access to only one database
i.e. only one database is selected in the properties.

They still seem to be able to access the system databases such as Master , Tempdb etc.
Any other databases I have created they cant access , as I would have expected.

[S@NSIS]

Have you taken them out of the public role in the system databases?

[DaveR]

i dont see where you can remove them from the public role.
if i open the 'master' database and go to roles and select Public and then its properties ,the two users of the role are dbo and guest.
there seems to no mention of my windows group of users.

[S@NSIS]

Remove the Guest account.

Unless you want guests to browse your database!!!

[DaveR]

I founf this piece in books online.it says you must have a guest user on master and tempdb.

"Permissions can be applied to the guest user as if it were any other user account. The guest user can be deleted and added to all databases except master and tempdb, where it must always exist. By default, a guest user account does not exist in newly created databases. "

This seems a bit strange , that anybody can who can access a database on the server will also by default get access to master and tempdb.

any ideas??

[S@NSIS]

doh...stupid me..

BOL is correct. If you user can't see the master or tempdb database then they won't be able to get anything from any other database!!

Example:

The user asks to run a stored proc from DatabaseX. DatabaseX has to then query master.sysobjects to determine what to get. If the user doesn't have permission to the master database then DatabaseX can't see sysobjects thus can't retrieve any data.

Basically, you will have to implement some security at the user's end that stops them from going direct into the Master and TempDB databases.

S@NSIS