Does this make any sense? 2

[innmedia]

Hi All,

I have an Access front end to SQL Server back end. Part of the processing is to create tables on the back end.

In one setup, at one of the offices, the application works fine.

At another office, soem tables don't appear. This is the key point. It is not that all tables do not appear, it is that some do not appear. I assume this means it is not a permissions issue. All the tables are to appear in one database.

Any ideas how this can be?

Thanks everyone!

 

[dbomrrsm]

ity could be a permissions issue as one office may log in one way which has permissions and the other office another where their permissions can not see these missing tables.

[blue]DBomrrsm[/blue]

 

[innmedia]

But can this be? Each application makes tables, let's say 10 tables. The user at the office in question - when the processing is complete will only see 7 of the tables in Enterprise Manager. There is nothing particularly different among the 10 tables. THey all are data queried out of a master table.

If the user can see 7 of them, why not the other 3?

I believe the tables are created though, because in the other office, using the exact same application and parameters, a user gets all 10 tables.

 

[SQLBill]

Yes, it can be. You can assign a user access to only certain tables in a database.

Check the users permissions. In Enterprise Manager, expand the database with the problem. Find Users. Click on that. Find the users name. Double-click on the user's login. Check that the user has permissions on all the tables.

Now check what Roles the user might be part of. Then go to Roles and check the permissions the Roles have.

Let's say UserX has access to all 10 tables.
Role1 has access to 7 tables but is DENYed access to the other 3.
UserX is a member of Role1. UserX will not be able to see the 3 tables that are DENYed to the role they are a member of.

-SQLBill

 

[innmedia]

Could these type of permissions become hit and miss like this for no reason? Simple Create Table SQL statements are being passed from Access to SQL Server to make the tables. Why / how could permissions be set different on some of the tables. Nothing in the code is dealing with permissions.

Ideas?

Thanks.

 

[SQLSister]

What I don't understand is why you would have a user application making tables to begin with. This is a very poor practice.

Since it is creating tables, the user clearly has create table permissions. So the table creations appear to be failing for some reason. Have you checked the error logs?

The possibility exists that the queries are timing out.

If you really want to see what is happening when the user sends the command to make the tables, suggest you turn on profiler and see what events are happening.

IS it always the same tables it doesn't create?

Questions about posting. See faq183-874

 

[SQLBill]

When do you create the user? A user has to be given permission to the table at some time and it's usually after it's created.

I would start with checking permissions.

-SQLBill

 

[innmedia]

I am setting the timeout to 0, which means indefinite:

conn.CommandTimeout = 0

When I try to run Profiler I get a "General Network Error". The installation I am on right now is a SQL developer edition running on my PC.

 

[innmedia]

Creating the user - I am the developer, not the network person. This is for a small company - a couple of users. I am not sure how SQL was installed, but it seems that they can get on OK with whatever password they have.

Makes sense?