2

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?

modernzombie
  • 173
  • 1
  • 7
  • 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

2 Answers2

2

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.

Mark Broadbent
  • 399
  • 1
  • 4
0

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.

mike42
  • 86
  • 7
  • 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