Post reply

Is it possible to limit view of databases on server in SSMS?

  • October 15, 2009 at 9:08 am

    #212121

    Hello -

    I have a user that was allowed Query Analyzer access in SQL 2000 so that she could create views and look at the database structure in a single database on the server so that she could do report writing using Crystal Reports and run simple queries and save the results. Since we've upgraded to SQL 2005, she's continued to use Query Analyzer but it has limitations due to the version change, e.g. she can't right click on an item in the object explorer and script it. Obviously, she can use sp_helptext etc. but she's complaining about losing functionality.

    We have been reluctant, however, to give her access to SSMS because we'd prefer that she not see the names of the other databases on the server. She doesn't have any rights to the other databases but she can still see them in the list of databases.

    Is there anyway of preventing her from seeing the other database names? Or is there a more appropriate tool for the tasks described?

    Thanks in advance for any information you could provide!

    Sam T

    Sam

  • October 20, 2009 at 1:52 am

    #1068008

    I dont think you can restrict somebody from seeing the names of databases. Is there any harm in that?

    You can create a development database for the developer on another server and let her work on that. Then somebody who can see 🙂 the other databases can run the script of the changes that the developer has done.

    "Keep Trying"

  • October 20, 2009 at 4:55 am

    #1068082

    You can disable the metadata, which means the user won't see any objects on the server at all -

    REVOKE VIEW ANY DATABASE FROM ----

    She will be able to run sql queries, but won't be able to see ANY databases, tables, sps etc. I use this to allow developers/support to get read only access to production tables to troubleshoot problems.

    This doesn't seem to run at a database level - only at the server, so probably not a great solution, but the only one I'm aware of.

    The only other option would be to find a third party offering.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply