How to Create a Minimal user in DB with no access to view Master DB

Hi,

We are using SQL Server 2005 Management Studio.

I created a Minimal User in an application DB. The user will access tables through stored procedures.

I do not want this user to view any other objects including objects in the Master DB.

I can prevent the minimal user from viewing objects from our application DB.

How do you prevent the minimal user from viewing objects in the Master DB?

Thanks.

Tim.

If u want a particular user should not access Master DB, do not add the user to master DB at all. We generally never add users to master db. Thump rule is that u should control permissions using Role. Create a new role add this user to that role and give permission to Role.

Madhu

|||

The user I setup is not in the Master DB. It seems there are default settings in Public on the Master DB that allow access to view tables within Master. Guest is a member of Public, therefore, users in other DB's can view these tables in Master. I want to know how to prevent this from happening. Or, does it really matter?

Tim.

|||

Why do you care if the user sees objects in master? Those objects are system (you should not store your user data in master, or if you decide to store it, just deny permissions on it to guest) and they are present in any installation, so there's no secret about their existence. The actual contents of the system catalogs are protected via catalog security - so even if your user sees a catalog, he'll only see the entries corresponding to entities he has permissions on. I don't think you should care about this, but if you are having a certain scenario in mind where you think that the information that is visible makes a system vulnerable, let us know about it.

Thanks

Laurentiu

|||

Hi Laurentiu,

Thanks for your answer. My concern was with the access that public has in the master DB. Is there a list or document that shows the initial settings of a master DB? I would like to compare the public permissions in my Master DB with the ones that come with SQL Server 2005 standard?

Is there a rule of Thumb that applies here?

Tim.

|||

You could make a fresh installation on another machine and record the list of permissions, then compare it against your current installation. I don't think there is a published list - it would be hard to maintain from release to release, as system objects are added or dropped in service packs as well.

For the public server role, things are pretty simple, as he only gets VIEW ANY DATABASE and some CONNECT permissions; for the public database role, things are more complicated - on my system I have 1664 various grants of SELECT or EXECUTE permissions. Things would get even more complicated if you install applications that create new objects in master and thus add new grants. However, you could write dedicated scripts to check the list of grants against a copy and report any changes, then you could run these scripts periodically.

Thanks

Laurentiu