SQL 2005 Default Database

Discussion in 'Databases' started by Takeshi Eto, Jul 7, 2008.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Takeshi Eto

    Takeshi Eto DiscountASP.NET Staff

    Yea, we think that sucks too. Unfortunately, it is a known issue/bug and we have spoken with Microsoft repeatedlyabout it. Hopefully Microsoft will provide a fix for this issue but seeing that their focus is on SQL 2008 right now, I'm not sure if we'll see anything soon. And BTW, from my understanding, this issue doesn't go away with SQL 2008 either.

    Eric
    http://www.DiscountASP.NET - Microsoft Gold Certified Partner
    - asp.netPRO Magazine 2008 Readers' Choice Award for Best ASP.NET Hosting Service
    - asp.netPRO Magazine 2007 Readers' Choice Award for Best ASP.NET Hosting Service
    - Visual Studio Magazine 2007 Readers' Choice Award for Best .NET Hosting Service
     
    Takeshi Eto, Jul 7, 2008
    #1
  2. wisemx

    wisemx

    Correct...still exists on SQL Server 2008.
    But it's more secure. [​IMG]
     
    wisemx, Jul 7, 2008
    #2
  3. PixelPlex

    PixelPlex

    Lame!
     
    PixelPlex, Jul 7, 2008
    #3
  4. PixelPlex

    PixelPlex

    When I sign in to my database using SQL Server Management Studio, I see a ton of other databases for all the other sites signing up. Of course, I cannot access these databases. Is there any way for me to filter this view so that I see only my database? It kinda sucks to have to go digging for it. I tried setting the default database for the connection but it doesn't seem to do anything.

    Thanks.
     
    PixelPlex, Jul 7, 2008
    #4
  5. Takeshi Eto

    Takeshi Eto DiscountASP.NET Staff

    We talk to the Microsoft SQL team every week - we'll let them know of your opinion of this issue.



    Eric
    http://www.DiscountASP.NET - Microsoft Gold Certified Partner
    - asp.netPRO Magazine 2008 Readers' Choice Award for Best ASP.NET Hosting Service
    - asp.netPRO Magazine 2007 Readers' Choice Award for Best ASP.NET Hosting Service
    - Visual Studio Magazine 2007 Readers' Choice Award for Best .NET Hosting Service
     
    Takeshi Eto, Jul 7, 2008
    #5
  6. FlyTheBlueSky

    FlyTheBlueSky

    Add me to the list... a default database can't be that hard to implement.
     
    FlyTheBlueSky, Jul 7, 2008
    #6
  7. Takeshi Eto

    Takeshi Eto DiscountASP.NET Staff

    Well, the problem is that Microsoft does not design software for hosting businesses. Microsoft designs software, like SQL, for enterprise companies. For enterprise companies , they want to see all the databases so Microsoft never really thought to put not displaying the databases on their original design specs. But the good thing is that Microsoft has been getting better about supporting hosters but that is really a recent thing.


    Eric
    http://www.DiscountASP.NET - Microsoft Gold Certified Partner
    - asp.netPRO Magazine 2008 Readers' Choice Award for Best ASP.NET Hosting Service
    - asp.netPRO Magazine 2007 Readers' Choice Award for Best ASP.NET Hosting Service
    - Visual Studio Magazine 2007 Readers' Choice Award for Best .NET Hosting Service
     
    Takeshi Eto, Jul 8, 2008
    #7
  8. wisemx

    wisemx

    Don't forget to vote:
    http://weblogs.asp.net/jgaylord/archive/2008/05/21/sql-management-studio-shows-all-databases.aspx
    Please voteat: http://tinyurl.com/6pk74q

    New SQL Server server-level permission:
    http://msdn.microsoft.com/en-us/library/ms189077.aspx

     
    wisemx, Jul 8, 2008
    #8
  9. wisemx

    wisemx

    Good point bro.
    I'd also like to mention how the current methodsare so much easier than how we used to do it.
    Tiny little dialog box you had to trudge through pipes or instance names in, and then it still choked. [​IMG]
     
    wisemx, Jul 8, 2008
    #9
  10. PixelPlex

    PixelPlex

    In SQL2005 management studio there is a filter icon that is greyed out on the object explorer window. In SQL2000, that icon used to be able to filter the view. Why they removed that feature I will never know.

    I am guessing that there might be a registry setting that might work. Anyone have any clues?
     
    PixelPlex, Jul 8, 2008
    #10
  11. FlyTheBlueSky

    FlyTheBlueSky

    The thing is, every username has a default database assigned to the username.
    That's the default database used for queries, so why not select the database in the object explorer tree too.

    Anyway, in the bigger scheme of things it is not important, but it is an annoyance.
     
    FlyTheBlueSky, Jul 8, 2008
    #11
  12. Takeshi Eto

    Takeshi Eto DiscountASP.NET Staff

    Thefix for SQL 2000 was a patch that Microsoft released a looooong time - a couple of years after SQL 2000 was out. Have no idea what happenned with SQL 2005. We were talking with Microsfot SQL team about that very issue back then before the launch of SQL 2005 too. They acknowledged the issue but it never made it into the release of SQL 2005. Fast forward to today - again no luck. So, from what I gather from all this, its not a trivial fix or they would have addressed it already and it isn't the most important priority as far as design specs.



    Eric
    http://www.DiscountASP.NET - Microsoft Gold Certified Partner
    - asp.netPRO Magazine 2008 Readers' Choice Award for Best ASP.NET Hosting Service
    - asp.netPRO Magazine 2007 Readers' Choice Award for Best ASP.NET Hosting Service
    - Visual Studio Magazine 2007 Readers' Choice Award for Best .NET Hosting Service
     
    Takeshi Eto, Jul 8, 2008
    #12
  13. PixelPlex

    PixelPlex

    I attached a screenshot of my SSMS permissions screen. I tried to deny the permission but received an error saying I didn't have grant permissions.
     

    Attached Files:

    PixelPlex, Jul 9, 2008
    #13
  14. PixelPlex

    PixelPlex

    It doesn't sound scary to me. It sounds like someone forgot to revoke that permission in the database setup script and it's a bug DASP hasn't gotten around to fixing. We aren't supposed to be able to see other people's database names.
     
    PixelPlex, Jul 9, 2008
    #14
  15. PixelPlex

    PixelPlex

    OOOOH I just found something interesting.

    If you right click on any database in the list and choose "Tasks/Generate Scripts..." It shows you a filtered list of only the databases you have access to. Apparently, I have access to another customer's database! I verified that I could, in fact, go into their database and open all their tables and read their private data. I would post a screenshot but I have a feeling that might get me in trouble.
     
    PixelPlex, Jul 9, 2008
    #15
  16. wisemx

    wisemx

    Are you sure?
    If you try to generate scripts on a DB you are not privileged for it should generate scripts for YOUR default.
    Do that again and Look closely then please report back. [​IMG]
     
    wisemx, Jul 9, 2008
    #16
  17. PixelPlex

    PixelPlex

    I didn't try to generate scripts for the other user's database, I just found out that I had access to it by going to the generate scripts window. I verified that I had access to her database by browsing into her database and was able to open her tables and read her blog entries.

    Go to the generate scripts window yourself and you may notice you have access to another user's database. Maybe not. Somehow I do.
     
    PixelPlex, Jul 9, 2008
    #17
  18. wisemx

    wisemx

    If you did have access it was because that customer, not DASP, allows access.
    Contact Support and verify that, give them full details.
    Salute,
    Mark
     
    wisemx, Jul 9, 2008
    #18
  19. wisemx

    wisemx

    As is on each of the SQL Servers we should see View All.
    The permissions should need to be made system wide.
    Can you imagine being a DASP DBA and experimenting with a change like that? Scary. [​IMG]
     
    wisemx, Jul 9, 2008
    #19
  20. Takeshi Eto

    Takeshi Eto DiscountASP.NET Staff

    When we set up a sql database it is secure. Customers have DBO rights so they can set permissions, which means they can also give guest access. Most of the time this happens its by accident. Please let our tech support know of any database that you have access to that you shouldn't have access to. Remember folks, with great power,cmoes great responsibility.


    Regarding the database list issue, it is a known "issue/bug" that Microsoft acknowledges - but in other circles of Microsoft customers(like enterprise customers) it is a feature that they wanted in the sql product. We can't really "fix" it. And with ALL the issues that the SQL product team has to deal with, it is one that is low in priority. We continue to "bug" Microsoft about it every week on our phone calls with them. We would encourage you to let your voice be known too - answer some blog posts about it, post in sql forums ...etc. Hopefully, Microsoft will make it a priority to fix sooner than later. (Like I said before, we waited a couple of years for Microsoft to fix the issue with SQL 2000).
    Eric
    http://www.DiscountASP.NET - Microsoft Gold Certified Partner
    - asp.netPRO Magazine 2008 Readers' Choice Award for Best ASP.NET Hosting Service
    - asp.netPRO Magazine 2007 Readers' Choice Award for Best ASP.NET Hosting Service
    - Visual Studio Magazine 2007 Readers' Choice Award for Best .NET Hosting Service
     
    Takeshi Eto, Jul 10, 2008
    #20
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page