"#Name?" in all the fields with a linked table in Access from SQL Server

[malibu65k]

I'm linked to an SQL Server, using MS Access. All the tables are linked to the same db and I can open and view the data in all the tables except one. All the fields for all the records show #NAME?. I first get an ODBC - Call failed! Then the table opens and I get all the #NAME? in the records. I re-link several times and I still get the same thing.

When I try to select from the table in SQL Server Management environment I get and error...

Msg 916, Level 14, State 1, Line2
Server user '%s' is not a valid user in database '%s'

But I am a valid user, the table is in the same database as the others and the others I have no problem accessing. Why would I be able to access all of them except one?

Thanks!

 

[simian336]

Possibly a field type that Access can't handle or and encrypted field.

Look at the fields on the sql server table and verify all the fields are a normal type.

Simi

 

[malibu65k]

I can't even open it to view the records in SQL Server Management either. When I try to do a select from I get this error...


Msg 916, Level 14, State 1, Line2
Server user 'user1' is not a valid user in database 'mydatabase'

 

[SQLBill]

Without having access to your server it will be difficult to troubleshoot this. But...

Go to Security>Logins. Find your login and open it. Go to the User Mappings. Confirm you have access to the database. Next do the same on the database (Security>Users). What permissions do you have? Do they match with the server level permissions?

Can you view the table through the SQL Server Management Studio interface?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Whoops..just saw you already tried to view the table in SSMS. Do you have admin permissions? Can you use that to check if you have been denied permissions on the table?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[malibu65k]

I do have admin permissions. I have access to all tables in SQL Server Management and read/write access with MS Access. Except for this one table in the same db, I get the #NAME? in all the fields in all the records in Access when I try to open it and the error above in SSMS.

 

[SQLBill]

Is the table owned by someone else? Most tables are owned by dbo, which means they are accessible to anyone who is not denied permissions on them. However, if you are creating a table and don't create it as dbo.<tablename>, it will be created using your login and only accessible to you. For example: malibu65k.mytable. It is possible the table is owned by a user and not by dbo. If you go to Databases > <database name> > Tables....can you see the table you are looking for? What schema is it a part of?

With admin access, you really should be able to see any table in any database. So you may want to make sure you are logging in with your admin account.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[malibu65k]

I looked at the properties for the table, it was checked for dbo to have access and it showed as "Public". My name was in the list but not checked for access. But the other tables that I can open don't have this in the properties. The db Admin told me that I was listed as the owner and I shouldn't have any problems accessing the table. I can see the table. I right click on it and select first 1000 rows and I get the error.

 

[SQLBill]

It is really sounding like you got denied or revoked from using SELECT on that table.

Run this on the database, replace mytable with your actual tablename

EXEC sp_table_privileges @table_name = 'mytable';

Then look through the list to see if you have been denied or revoked the select permission.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[malibu65k]

DB Admin looked and said I had full access. He couldn't figure it out either. He just redid the permisions using the same password and Username as the Production and now I can access that table in Development. I have access to both now. He said after researching it, it has something to do with a bug or needed patch. I don't understand any of that stuff.