SQL Server 2008 connection problem

Is your ODBC connection properties set up with windows authentication or SQL account is used?

I am using this piece of code

 stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
 CurrentDb.TableDefs.Append td
 AttachDSNLessTable = True

The SQL Server database is using Windows Authentication.

Trusted_Connection=Yes
Ok, if your users are using integrated windows authentication and cannot see objects in database it means that they do not have sufficient priviledges to it.
For test purposes please add user to db_datareader built in database group.

If you want to manage users access you can create your own database role(s) and grant all priviledges as you wish. Then you can just add membership of that role(s) to your users.
You can also add them to built in database groups.
http://www.mssqltips.com/tip.asp?tip=1900

Daniel
Need to go out now. Thanks for your prompt reply and help. I'll try this later.

What do you mean by "built in database group"?

I mean SQL Server fixed database roles, sorry for disinformation.

Daniel
Sorry, I should you need to be explicit. Don't know even how to do this

Sure, look at screenshot below. In your login properties at the user mapping section you have database roles. Each role in the screenshot is fixed datbase role. In the link i posted above (http://www.mssqltips.com/tip.asp?tip=1900) you'll find explanations of each role's permissions.
You can create your own database roles with defined priviledges to create more specific rights in database. By assigning user to a role you can grant your own predefined permissions.

I'm sorry but I still don't understand!

Do I need to assign roles to users? Shouldn't I just be able to create the database, add tables, and then my users be able to link to these tables via MS Access. I haven't had to worry about user roles before. If I do have to allocate roles, please explain to me how to do this, in very simple terms.

Thanks

You need to add your users windows accounts as logins in SQL Server. Then in each login properties, as showed before, you need to map each login to database to create user. When your logn is mapped as user in database he/she needs to have permissions in that database. A role is a set of permissions for user in a database.
Following query shows users and their roles in your db:

USE <your db name>
SELECT
dbp.name, dbp.type_desc, dbpp.name, dbpp.type_desc, dbpp.is_fixed_role
FROM sys.database_role_members rm
JOIN sys.database_principals dbp ON rm.member_principal_id = dbp.principal_id
JOIN sys.database_principals dbpp ON rm.role_principal_id = dbpp.principal_id

Select allOpen in new window

I get this as a result:
dbo      WINDOWS_USER      db_owner      DATABASE_ROLE      1

what does this mean please?

Attached are the roles in my database. Is this correct please?
SQL-Server1.bmp

You used code against ReportServer database but in screen attached you showed another database.
Can you execute this code please:
   

USE [RPI Data_beSQL]
SELECT dbp.name AS username,dbp.type_desc AS user_type_desc,
sp.name AS login_name, sp.default_database_name AS login_default_db_name,
dbpp.name AS role_name, dbpp.type_desc AS role_type 
FROM sys.server_principals AS sp JOIN sys.database_principals AS dbp 
ON sp.sid=dbp.sid JOIN sys.database_role_members AS rm
ON rm.member_principal_id=dbp.principal_id
JOIN sys.database_principals AS dbpp ON rm.role_principal_id = dbpp.principal_id
GO
SELECT name,type_desc,is_disabled,default_database_name FROM sys.server_principals 
WHERE type IN ('U','S') AND name NOT LIKE 'sa'
GO

Select allOpen in new window

Sorry about that. I did run it aganst RPI, but then thought I'd try it against another too.

I ran the code and got this
##MS_PolicyEventProcessingLogin##      SQL_LOGIN      1      master
##MS_PolicyTsqlExecutionLogin##      SQL_LOGIN      1      master
NT AUTHORITY\SYSTEM      WINDOWS_LOGIN      0      master
RPINT\administrator      WINDOWS_LOGIN      0      master
NT AUTHORITY\NETWORK SERVICE      WINDOWS_LOGIN      0      master
marc_admin_20110208010141      SQL_LOGIN      0      master
Feb-April2011_arch_20110208010218      SQL_LOGIN      0      Feb-April2011
Feb-April2011_brws_20110208010219      SQL_LOGIN      0      Feb-April2011
2005-2010_arch_20110208042934      SQL_LOGIN      0      2005-2010
2005-2010_brws_20110208042935      SQL_LOGIN      0      2005-2010
2005-2009_arch_20110208043349      SQL_LOGIN      0      2005-2009
2005-2009_brws_20110208043350      SQL_LOGIN      0      2005-2009
2020-2009_arch_20110209015128      SQL_LOGIN      0      2020-2009
2020-2009_brws_20110209015129      SQL_LOGIN      0      2020-2009
MA_001_arch_20110209024635      SQL_LOGIN      0      MA_001
MA_001_brws_20110209024635      SQL_LOGIN      0      MA_001
Test_arch_20110209030634      SQL_LOGIN      0      Test
Test_brws_20110209030635      SQL_LOGIN      0      Test
MA_001_01_arch_20110210033358      SQL_LOGIN      0      MA_001_01
MA_001_01_brws_20110210033358      SQL_LOGIN      0      MA_001_01
MA_001_02_arch_20110210041521      SQL_LOGIN      0      MA_001_02
MA_001_02_brws_20110210041521      SQL_LOGIN      0      MA_001_02
MArcStore_2011_arch_20110210042027      SQL_LOGIN      0      MArcStore_2011
MArcStore_2011_brws_20110210042027      SQL_LOGIN      0      MArcStore_2011
MArcStore_2010_arch_20110210042223      SQL_LOGIN      0      MArcStore_2010
MArcStore_2010_brws_20110210042223      SQL_LOGIN      0      MArcStore_2010
MArcStore_2013_arch_20110210042959      SQL_LOGIN      0      MArcStore_2013
MArcStore_2013_brws_20110210043000      SQL_LOGIN      0      MArcStore_2013
MArcStore_2008_arch_20110210043600      SQL_LOGIN      0      MArcStore_2008
MArcStore_2008_brws_20110210043600      SQL_LOGIN      0      MArcStore_2008
MArcStore_2009_arch_20110210043831      SQL_LOGIN      0      MArcStore_2009
MArcStore_2009_brws_20110210043831      SQL_LOGIN      0      MArcStore_2009
RPINT\ray.claridge      WINDOWS_LOGIN      0      master
RPINT\kle      WINDOWS_LOGIN      0      master
RPINT\jr      WINDOWS_LOGIN      0      master
RPINT\sbb      WINDOWS_LOGIN      0      master
RPINT\cc      WINDOWS_LOGIN      0      master
RPINT\sc      WINDOWS_LOGIN      0      master
MArcStore_2007_arch_20110309052646      SQL_LOGIN      0      MArcStore_2007
MArcStore_2007_brws_20110309052646      SQL_LOGIN      0      MArcStore_2007

Can you check, what returns first part of my query (until first GO) for users RPINT\cc, RPINT\kle, RPINT\jr, RPINT\sbb, RPINT\sc - i suppose they are your Access user, right?

This is what i get:

dbo      WINDOWS_USER      RPINT\rtc      master      db_owner      DATABASE_ROLE

The users you ask for are not returned.

Ok, so users from RPINT\... are your Access users? What do you want to allow them to do in that database?
For each login you want to add permissions open Security folder in server tree level (not in database) and edit it's properties. Then as I posted above in User mapping tab select map square and in window below mark apropriate group.
RPINT\rtc has db_onwer role, you can look in it's properties to have an example :)

I just want them to be able to link to these tables from an Access front end. RPINT\rtc can link already. I presume because he set the database up.

I don't have User Mapping available as an option on my SQL Server

Daniel
All working now, thanks for your persistence.

So do I always have to set up individual users for each database? What if I had a database with 50 users, or even more?

Anyway, points awarded. If I could give more, I would!

Thanks!

It works as following:
you are creating login to sql server - it can be windows or domain group also
login has to be user in database to be able to perform operations in it

You can read more about security in SQl Server:
http://www.databasejournal.com/features/mssql/article.php/3713116/SQL-Server-Security-Model.htm