Post reply

Tables List not displaying

  • February 28, 2009 at 12:03 pm

    #200829

    MS SQL Server Management Studio Express (ver 9)

    SQL Server 2005

    Vista and Server 2003 (same database)

    Using an admin (sa) account

    When expanding Tables from Object Explorer .. no tables are shown

    Only on certain databases .. others perform fine

    Database DOES contain tables and in use via web CMS (DotNetNuke)

    Simply not able to manually manage tables from Management Studio

    (Long time) surface dweller with MSSQL server with ver 7, 2000

    I suspect permission issues or database was created via script from asp (web) that could cause this scenario.

    Thanks for the venue

  • February 28, 2009 at 7:32 pm

    #951557

    Hello,

    A couple of T-SQL statements to try from a query window connected to the DB in question:-

    Select * From INFORMATION_SCHEMA.TABLES

    Do you get a list of the Tables that you were expecting to see in the SMS tree view?

    If not then execute this statement, specifying one of the Table names that you expect to be in the DB:-

    Select * From fn_my_permissions('Your Table Name Here', 'OBJECT')

    Do you see a record with “View Definition” in the Permission_Name column?

    (Please note that it is important that you are connected to the correct DB for these statements to work as needed).

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • March 1, 2009 at 7:19 am

    #951593

    Hey John

    NEGATIVE on both queries

    (0 row(s) affected)

    Tested with other databases .. results as expected

    Running from the problem database .. no results on both queries

    Tested on 2 separate machines (same database) .. same results

    Logged in as sa(sysadmin role) on one

    Logged in using Window Auth (sysadmin role) on the other

    mdf file exists

    Content management system via http functions fine with database

    big trouble??

  • March 1, 2009 at 7:50 am

    #951595

    very wierd;

    I have half a dozen DotNetNuke installations either locally, for friends, or on my hosted web site;

    if you are in as sa or admin you should see everything;

    when i restore a backup from my hosted account, all the table are in a schema named "stormweb", so they are stormweb.Modules, stormmweb.Users , etc for example; I usually change the owner to dbo to play locally...but i can still see all the objects, regardless of the schema/owner.

    could it be that you are restoring just the first backup in from a file that contains more than one backup, so the first backup is an empty database, but a later backup in the same file has the tables and the data?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 1, 2009 at 12:45 pm

    #951613

    Hello again,

    Hmm, I guess it isn’t a “big” problem as your application is working fine. It would be good though to be able to explain the cause - just to be on the safe side.

    To pursue the Permissions related theory, may be take a look in the Security node in SSMS. Have a scout around the properties for the Logins that you are using (especially the SA) to see if they are Mapped to the DB and if so, what Roles they have been allocated too. For example check if the Login has been added to the db_denydatareader Role for the DB.

    Does the application add any Custom DB Roles, or Logins for Windows Groups?

    If this issue only appeared for Windows users it could have been related to membership of a Group that had specifically had permissions denied, but I don’t see how this would happen for the SA Login.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • March 1, 2009 at 1:58 pm

    #951617

    John

    Yes to all that .. going cross-eyed

    re: application controlled roles ... I think there is merit to that

    I have now been able to reinstate the application's authentication mode at interface and internally

    spoofed page control by flipping/replacing aspx files .. so simple it's stupid .. gave me a prompt from which to gain access with standard auth which in turn allowed me to set things straight

    SQL database with SMS still a hot ticket and will continue to explore .. but for now I have admin functionality through application interface .. VALUABLE LESSONS LEARNED .... don't change the door and lock without first obtaining a key otherwise you end up having to replace the door and frameset 😉

    I want to thank you and Lowell for offering help..cheers

    If I find anything I will be certain to post.

Viewing 6 posts - 1 through 5 (of 5 total)

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