Restricting databases seen in SQL 2005

[markknowsley]

I've installed SQL Server 2005 Management Studio Express on a colleagues PC and granted her access to only one database on one server.

When she expands the server, she can see the full list of databases on that server (although if she tries to open any of the other databases that I haven't granted rights to she gets an 'access denied' message).

Is there any way to set up SQL so that she can only see the databases that she has rights to work on - so that she won't even no that the restricted databases even exist? This is more for neatness rather than a real business need, but I just thought I would ask.

Mark.

 

[ptheriault]

Nope, EM can see all the databases. It will then use the login account to authenticate their rights.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

One way you could do this is to install SQL Server again with a named instance. Grant the user rights to one of the instances but not the other.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markknowsley]

Hmmm.... I can imagine the conversation:

me: I want to reinstall SQL Server
dba (looking worried): why?
me: I want to set it so that people can only see the db's they've got permissions to work on
dba: rearrange the following words - 'way', 'no'

Maybe I'll have a go at setting this up on my laptop - thanks for the tip.

 

[ptheriault]

Mark,
I can tell you as the Sr DBA where I work that that your conversation is very mild to how it would really go.

Good Luck!




- Paul

- If at first you don't succeed, find out if the loser gets anything.