Solved

SQL Server 2008 connection problem

Posted on 2011-03-09
23
265 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:rick_danger
  • 12
  • 11
23 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080589
Is your ODBC connection properties set up with windows authentication or SQL account is used?
0
 

Author Comment

by:rick_danger
ID: 35080614
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080652
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080685
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
 

Author Comment

by:rick_danger
ID: 35080690
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080693
I mean SQL Server fixed database roles, sorry for disinformation.
0
 

Author Comment

by:rick_danger
ID: 35082254
Daniel
Sorry, I should you need to be explicit. Don't know even how to do this
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35082280
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
 

Author Comment

by:rick_danger
ID: 35121496
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35121676
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
 

Author Comment

by:rick_danger
ID: 35122092
I get this as a result:
dbo      WINDOWS_USER      db_owner      DATABASE_ROLE      1

what does this mean please?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:rick_danger
ID: 35122166
Attached are the roles in my database. Is this correct please?
SQL-Server1.bmp
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35122581
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
 

Author Comment

by:rick_danger
ID: 35123045
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35123156
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
 

Author Comment

by:rick_danger
ID: 35123198
This is what i get:

dbo      WINDOWS_USER      RPINT\rtc      master      db_owner      DATABASE_ROLE

The users you ask for are not returned.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35123284
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
 

Author Comment

by:rick_danger
ID: 35123314
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
 

Author Comment

by:rick_danger
ID: 35124005
I don't have User Mapping available as an option on my SQL Server
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35125667
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
 

Author Comment

by:rick_danger
ID: 35127217
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
 

Author Closing Comment

by:rick_danger
ID: 35127220
Thanks!
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35127279
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now