MS Sqlserver 2005: Displaying only dbs user has a right to?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 26 Apr 2007 19:52 GMT

Hi;

My company just installed MS SQLServer 2005 ( see below the dotted
line ).

When a user logs into management studio all of the databases on the
server are displayed in the right hand column.

What can we do to have only the databases the user has rights to,
display?

Thanks much in advance for any info.

Steve

-------------------------------------------------------------------
Microsoft SQL Server Management Studio        9.00.1399.00
Microsoft Analysis Services Client Tools    2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML                        2.6 3.0 4.0 6.0
Microsoft Internet Explorer                7.0.5730.11
Microsoft .NET Framework            2.0.50727.42
Operating System                5.1.2600

Reply to this Message

Erland Sommarskog - 26 Apr 2007 23:09 GMT

> My company just installed MS SQLServer 2005 ( see below the dotted
> line ).
[quoted text clipped - 4 lines]
> What can we do to have only the databases the user has rights to,
> display?

What you can do is to revoke the permission VIEW ANY DATABASE from public,
or DENY this permission to the users in question. Alas, the user would then only see tempdb and msdb (or was it master?), databases he owns, and
possibly his current database. He would have to issue a USE database in
the dark to get there.

This is not an uncommon question, so I filed a suggestion for an improvement
in SQL Server. You can vote on it at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message