Rick Danger
asked on
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
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
Is your ODBC connection properties set up with windows authentication or SQL account is used?
ASKER
I am using this piece of code
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Set td = CurrentDb.CreateTableDef(s tLocalTabl eName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
The SQL Server database is using Windows Authentication.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Set td = CurrentDb.CreateTableDef(s
CurrentDb.TableDefs.Append
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.
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
You can also add them to built in database groups.
http://www.mssqltips.com/tip.asp?tip=1900
ASKER
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"?
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.
ASKER
Daniel
Sorry, I should you need to be explicit. Don't know even how to do this
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.
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.
ASKER
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
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:
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
ASKER
I get this as a result:
dbo WINDOWS_USER db_owner DATABASE_ROLE 1
what does this mean please?
dbo WINDOWS_USER db_owner DATABASE_ROLE 1
what does this mean please?
ASKER
Attached are the roles in my database. Is this correct please?
SQL-Server1.bmp
SQL-Server1.bmp
You used code against ReportServer database but in screen attached you showed another database.
Can you execute this code please:
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
ASKER
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_PolicyEventProcessing Login## SQL_LOGIN 1 master
##MS_PolicyTsqlExecutionLo gin## 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_2011020 8010218 SQL_LOGIN 0 Feb-April2011
Feb-April2011_brws_2011020 8010219 SQL_LOGIN 0 Feb-April2011
2005-2010_arch_20110208042 934 SQL_LOGIN 0 2005-2010
2005-2010_brws_20110208042 935 SQL_LOGIN 0 2005-2010
2005-2009_arch_20110208043 349 SQL_LOGIN 0 2005-2009
2005-2009_brws_20110208043 350 SQL_LOGIN 0 2005-2009
2020-2009_arch_20110209015 128 SQL_LOGIN 0 2020-2009
2020-2009_brws_20110209015 129 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_20110210033 358 SQL_LOGIN 0 MA_001_01
MA_001_01_brws_20110210033 358 SQL_LOGIN 0 MA_001_01
MA_001_02_arch_20110210041 521 SQL_LOGIN 0 MA_001_02
MA_001_02_brws_20110210041 521 SQL_LOGIN 0 MA_001_02
MArcStore_2011_arch_201102 10042027 SQL_LOGIN 0 MArcStore_2011
MArcStore_2011_brws_201102 10042027 SQL_LOGIN 0 MArcStore_2011
MArcStore_2010_arch_201102 10042223 SQL_LOGIN 0 MArcStore_2010
MArcStore_2010_brws_201102 10042223 SQL_LOGIN 0 MArcStore_2010
MArcStore_2013_arch_201102 10042959 SQL_LOGIN 0 MArcStore_2013
MArcStore_2013_brws_201102 10043000 SQL_LOGIN 0 MArcStore_2013
MArcStore_2008_arch_201102 10043600 SQL_LOGIN 0 MArcStore_2008
MArcStore_2008_brws_201102 10043600 SQL_LOGIN 0 MArcStore_2008
MArcStore_2009_arch_201102 10043831 SQL_LOGIN 0 MArcStore_2009
MArcStore_2009_brws_201102 10043831 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_201103 09052646 SQL_LOGIN 0 MArcStore_2007
MArcStore_2007_brws_201103 09052646 SQL_LOGIN 0 MArcStore_2007
I ran the code and got this
##MS_PolicyEventProcessing
##MS_PolicyTsqlExecutionLo
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_2011020
Feb-April2011_brws_2011020
2005-2010_arch_20110208042
2005-2010_brws_20110208042
2005-2009_arch_20110208043
2005-2009_brws_20110208043
2020-2009_arch_20110209015
2020-2009_brws_20110209015
MA_001_arch_20110209024635
MA_001_brws_20110209024635
Test_arch_20110209030634 SQL_LOGIN 0 Test
Test_brws_20110209030635 SQL_LOGIN 0 Test
MA_001_01_arch_20110210033
MA_001_01_brws_20110210033
MA_001_02_arch_20110210041
MA_001_02_brws_20110210041
MArcStore_2011_arch_201102
MArcStore_2011_brws_201102
MArcStore_2010_arch_201102
MArcStore_2010_brws_201102
MArcStore_2013_arch_201102
MArcStore_2013_brws_201102
MArcStore_2008_arch_201102
MArcStore_2008_brws_201102
MArcStore_2009_arch_201102
MArcStore_2009_brws_201102
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_201103
MArcStore_2007_brws_201103
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?
ASKER
This is what i get:
dbo WINDOWS_USER RPINT\rtc master db_owner DATABASE_ROLE
The users you ask for are not returned.
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 :)
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 :)
ASKER
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.
ASKER
I don't have User Mapping available as an option on my SQL Server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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
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