SQL Enterprise Manager - Missing tables?!? (1 Viewer)

[SteveClarkson]

Hello all,

This is likely to be something really obvious, but I can't figure it out for the life of me!

I upsized my Access backend to our SQL (2000) server a few weeks ago, and everything has been going fine, I am accessing the data with no problems.
However, today, I went to edit one of the tables on SQL server, I just wanted to add a field to a table, but can't find the tables.

I can see the database in the list of databases (surprise surprise), but when I expand that database, the Tables section has all the "sys..." tables in there, but none of mine.

I have double-checked the ODBC connection on my PC, and it is pointing at that DB server.
Just to make sure, I have logged on to each of our four SQL servers (DB01, DB02, DB03 and DB04), and looked through all of them - only one of them (DB02) has my database on it, but with NO tables.

I can still open those tables up and edit data in them in Access, but cannot see them in Enterprise Manager at all.

I have also tried connecting to DB01, 3 and 4, and created a new registration to see if I could see the tables from another server, but no luck, still only the system tables.

The only slightly strange thing I can see is when I go into the ODBC connection the PC's, there are TWO entries for DB02. I have no idea why, we certainly don't have 2 servers called the same, and all the others appear in there OK.

I have also tried logging onto the server direct, both as domain administrator, and as myself (also a domain admin), but no change.

Does anyone have any bright ideas? I am at a loss!

 

[namliam]

Does the user you use to connect to the DB in the enterprise mananger have the proper rights to see the tables??

 

[SteveClarkson]

I would have thought so - but it might be worth checking.

As I say, I am logging in as domain admin, which should have access to everything, and as myself, who certainly has access to the data in those tables, as I can access them on my PC.

However, due to my limited knowledge of SQL - could you point me in the right direction to check these permissions?

Surely I wouldn't be able to see the system tables if I didn't have permission?

 

[namliam]

I am no where near a DBA or anything... I wouldnt know where to start looking for this.

Just a (very) advanced user, I have had simular problems not finding my tables only seeing sys and simular tables. It turned out to be a rights issue, our DBA solved it (eventually) but I cannot help you in finding it... Just offer advice to go look in that corner of the DB, becuase I have had that issue in the past.

Good Luck !

 

[SteveClarkson]

Well, it is reassuring at least that it is a problem at least ONE person has had before!!!

Glad I wasn't missing a "Hide all the tables, in a really annoying way" tick box or something!!!


I'll have ANOTHER root through all the permissions that i can find this afternoon, and see what happens!

Any more help from anyone would be much appreciated though!

 

[tehNellie]

One very quick suggestion is to right click the database in Enterprise Manager and select Refresh, I have noticed that it isn't very good at automatically refreshing itself to pick up new objects especially when those objects are created outside of Enterprise Manager.

As I say, I am logging in as domain admin, which should have access to everything, and as myself, who certainly has access to the data in those tables, as I can access them on my PC.

Depending on how your SQL server is configured domain admins won't necessarily have any implicit rights within the SQL server itself although by default MS mysteriously grants members of the local Administrators Windows Group System Admin rights on the SQL Server.

At a server level look at Security, Server Roles and the System Administrator Group. If BUILTIN\Administrators is listed then your Domain Admin Account should be a SQL Server Administrator.

You can also run SELECT user_name() from the query Analyser or from Access' query builder, this will display the DATABASE (not server) role that SQL Server thinks your account is a member of. If you're an admin it should return dbo. SELECT suser_sname() will return the account that you are currently connecting to the database through in case you aren't using Windows Auth and have a save username and password in Access and are using your windows account to connect to Enterprise Manager.

Failing that check the server logins tab for your Account/AD Group and check what permissions it has set.