SQL Server 2008 connection problem

I have recently upsized an Access database to SQL Server 2008. When I try to create an ODBC connection using the wizard it all works fine and I can link to the database tables. When my users try to, they don't see the dbo tables, only the system tables.

I am presuming that this is a permissions issue, as I set the database up, and my connection works fine. The administrator set up all 4 users as Users on the database in SQL Server, so if i go to the Security tab under the database I can see these users. I have been told that this is not the best way to set it up because if we want to add more users we will have to add them to the Users in SQL Server.

So, can somebody please let me know why my users cannot see the database tables, and what the best way to set the database is. It will be multi-user and all will need to be able to link to it from MS Access.

Permissions and security are not subjects I really understand in SQL Server, so the most explicit and easy to follow instructions will win the points!

Thanks

Rick
rick_dangerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Ok try that, assuming RPINT\... are your access users and you want them to also write, create objects etc. in the database:

 
USE [RPI Data_beSQL]
EXEC sp_addrolemember 'db_datawriter', 'RPINT\cc'
GO
EXEC sp_addrolemember 'db_datawriter', 'RPINT\jr'
GO
EXEC sp_addrolemember 'db_datawriter', 'RPINT\kle'
GO
EXEC sp_addrolemember 'db_datawriter', 'RPINT\sbb'
GO
EXEC sp_addrolemember 'db_datawriter', 'RPINT\sc'
GO

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
Is your ODBC connection properties set up with windows authentication or SQL account is used?
0
 
rick_dangerAuthor Commented:
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.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Daniel_PLDB Expert/ArchitectCommented:
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.
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
0
 
rick_dangerAuthor Commented:
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"?
0
 
Daniel_PLDB Expert/ArchitectCommented:
I mean SQL Server fixed database roles, sorry for disinformation.
0
 
rick_dangerAuthor Commented:
Daniel
Sorry, I should you need to be explicit. Don't know even how to do this
0
 
Daniel_PLDB Expert/ArchitectCommented:
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. login
0
 
rick_dangerAuthor Commented:
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
0
 
Daniel_PLDB Expert/ArchitectCommented:
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

Open in new window

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

what does this mean please?
0
 
rick_dangerAuthor Commented:
Attached are the roles in my database. Is this correct please?
SQL-Server1.bmp
0
 
Daniel_PLDB Expert/ArchitectCommented:
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

Open in new window

0
 
rick_dangerAuthor Commented:
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
0
 
Daniel_PLDB Expert/ArchitectCommented:
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?
0
 
rick_dangerAuthor Commented:
This is what i get:

dbo      WINDOWS_USER      RPINT\rtc      master      db_owner      DATABASE_ROLE

The users you ask for are not returned.
0
 
Daniel_PLDB Expert/ArchitectCommented:
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 :)
0
 
rick_dangerAuthor Commented:
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.
0
 
rick_dangerAuthor Commented:
I don't have User Mapping available as an option on my SQL Server
0
 
rick_dangerAuthor Commented:
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!
0
 
rick_dangerAuthor Commented:
Thanks!
0
 
Daniel_PLDB Expert/ArchitectCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.