up vote 2 down vote favorite
share [g+] share [fb]

I am not sure if what I want is possible but here we go.

At work we have a dedicated SQL Server 2008 box that host the various databases that we need to run. We have a remote person (lets' call them Bob) who needs to host a database on it. I have been asked to limit Bob's access to the SQL box but give him just enough access to control his database.

Currently I have setup a SQL login for Bob and made it the dbowner of Bob's database and not given access to anything else. When I login with Bob's SQL login to SSMS I can see all the other databases but can only access Bob's. I can, however, access and play with the master database which doesn't feel warm and fuzzy to me.

Is there a way to prevent Bob from seeing the names of the other databases and prevent him from having access to the master database?

link|improve this question
40% accept rate
Bob's login can connect to master? Is that the default database you've set for him? What do you mean by "Play with" ? – jl. Nov 10 '10 at 20:11
master is the main system database that store configuration information about the SQL Server instance. No I did not give Bob access to this database. Bob's login says he shouldn't have access. By "play with" I mean he has access to and can select records. – modernzombie Nov 10 '10 at 20:17
feedback

2 Answers

up vote 2 down vote

In SQL 2005 and 2008 the ability to grant/ remove rights to view databases was introduced and is called "VIEW ANY DATABASE"

http://msdn.microsoft.com/en-us/library/ms189077.aspx

You simply have to ensure the login in question that you want to restrict does not have direct or indirect (though group membership) rights to this server right.

link|improve this answer
feedback
up vote 0 down vote

I'm not sure about disabling the ability to see db's, but with respect to master Bob probably either (a) has explicit permission to master (b) has a server role or (c) is accessing as "guest". Checking a & b should be easy enough by viewing Bob's server login. Check c by running "select user_name()" in master while logged in as Bob.

link|improve this answer
Ok, a)Bob does not have permission to master b) Bob has a Server Role of "public" c) that query returned "guest". How would I prevent Bob from accessing master? – modernzombie Nov 10 '10 at 20:21
You dont. There is a lot in Master even a guest NEEDS to work properly. The question is not whether he accessed mater, but whether you opened master access to thinks that are not normal operational use. Master is not a "database". It contains configuration information. For example, to see how many files his own database has - master is accessed. – TomTom May 16 '11 at 17:22
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.