Limit View in SQL 2005

When adding users to the SQL Server, is there a way to limit what DB's the
user can actually view when using Enterprise Manager?
i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
list of DB's, they can't see Master, Model, or any others?
The objective is to let a company connect to their Database, without see
what other clients/databases are hosted on the same DB server.
ThanksHi Kevin,
This is indeed possible in SQL Server 2005 but the master and tempdb
databases will be visible to all users - there is no way you can get
around that. Every user needs to have access to these databases. It's
just the way SQL Server works.
In order to explain how to make USER databases invisible to others I am
attaching the following script. It simulates a situation with three
databases, each belonging to different companies. Each company has its
own login.
create database company1_db
create database company2_db
create database company3_db
go
create login company1 with password = 'password1234*'
go
create login company2 with password = 'password1234*'
go
create login company3 with password = 'password1234*'
go
--At this point, all three logins (company1, company2 and company3)
--can see all user databases (company1_db, company2_db and company3_db).
use master
go
deny view any database to public
--At this point, all three logins (company1, company2 and company3)
--can see just the master and tempdb databases.
use company1_db
go
sp_changedbowner 'company1'
use company2_db
go
sp_changedbowner 'company2'
use company3_db
go
sp_changedbowner 'company3'
--At this point, the three logins (company1, company2 and company3)
--can see the master and tempdb databases as well as their own database.
Hope this helps!
Jonathan
Kevin A wrote:
> When adding users to the SQL Server, is there a way to limit what DB's the
> user can actually view when using Enterprise Manager?
> i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
> list of DB's, they can't see Master, Model, or any others?
> The objective is to let a company connect to their Database, without see
> what other clients/databases are hosted on the same DB server.
> Thanks
>|||JPD (jpd@.nospamplease.com) writes:
> use master
> go
> deny view any database to public
> --At this point, all three logins (company1, company2 and company3)
> --can see just the master and tempdb databases.
> use company1_db
> go
> sp_changedbowner 'company1'
>
> use company2_db
> go
> sp_changedbowner 'company2'
>
> use company3_db
> go
> sp_changedbowner 'company3'
But the apparent problem with that is there for every company are several
logins.
What SQL 2005 offers is clearly not sufficient. For this reason I submitted
this entry on Connect a while back:
https://connect.microsoft.com/SQLSe...=2738
30
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx