Hi All, I have a SBS 2003 R2 premium with a SQL2005. when i login with a user on a client a get the error: Not associated with a trusted SQL Server connection. when i right click on the icon and select Run As, and use administrator it works. i found this http://support.microsoft.com/kb/889615 but that didn't help.authentication is on sql server and windows. what do i have to do to solve this? or do i have to grant access to the individual database objects that the user requires access to. How do you that and were? MarcH newbie to SQL
1) Create a Windows AD group for all the users that need access to the object (table/view) and put all the users to that group 2) Create a login on SQL Server based on the AD group you just created 3) Create a user in the database based on the login you just created 4) Give the appropiate rights on the objects for the user you just created. 5) Now you can use Windows Authentication when connecting from the client to the SQL Server. Gé "MarcH" wrote in message news:%23tQiIwq1IHA.2068@TK2MSFTNGP05.phx.gbl... > Hi All, > I have a SBS 2003 R2 premium with a SQL2005. > when i login with a user on a client a get the error: Not associated with > a trusted SQL Server connection. > when i right click on the icon and select Run As, and use administrator it > works. > > i found this http://support.microsoft.com/kb/889615 but that didn't > help.authentication is on sql server and windows. > what do i have to do to solve this? > > or do i have to grant access to the individual database objects that the > user requires access to. > How do you that and were? > > > MarcH > newbie to SQL >
I hope you can give me some details on the steps. > 1) Create a Windows AD group for all the users that need access to the > object (table/view) and put all the users to that group I created a group (SQL users). > 2) Create a login on SQL Server based on the AD group you just created where can i create a login on SQL ? Is this a user refered to the group (SQL users)? > 3) Create a user in the database based on the login you just created Where can i create that user? > 4) Give the appropiate rights on the objects for the user you just > created. That i will find when i know where to create the user > 5) Now you can use Windows Authentication when connecting from the client > to the SQL Server.
"MarcH" wrote in message news:OAQrKQ31IHA.2068@TK2MSFTNGP05.phx.gbl... >I hope you can give me some details on the steps. > >> 1) Create a Windows AD group for all the users that need access to the >> object (table/view) and put all the users to that group > > I created a group (SQL users). > >> 2) Create a login on SQL Server based on the AD group you just created > > where can i create a login on SQL ? Is this a user refered to the group > (SQL users)? SQL Server Management Studio, connect to the SQL Server, click on the SQL Server, click on '+' before 'Security', click with your right mouse button on 'Logins' and choose 'New Login' and click on the 'Search...' button to find your AD Group. When the AD group is filled in, change the default database to the database the group needs access to. Do NOT click on 'Ok' yet. > >> 3) Create a user in the database based on the login you just created > > Where can i create that user? Click on the right at 'User Mapping' and click on the database role membership which the users need. (db_datareader & db_datawriter for change permissions) and click on 'Script' on top of the window you see. (Why script and not OK? Because now you can see what happens, and you can save this script for later use when restoring the system if needed). Now click on 'Execute' or hit 'F5'. The login and user is created. > >> 4) Give the appropiate rights on the objects for the user you just >> created. > > That i will find when i know where to create the user Correct, you already have done it in step 3 ;) > >> 5) Now you can use Windows Authentication when connecting from the client >> to the SQL Server. > Good luck! Gé > >
Script can look like this: USE [master] GO CREATE LOGIN [<DOMAINNAME>\<GROUPNAME>] FROM WINDOWS WITH DEFAULT_DATABASE=[BeheerDB] GO USE [<DATABASENAME>] GO CREATE USER [<DOMAINNAME>\<GROUPNAME>] FOR LOGIN [<DOMAINNAME>\<GROUPNAME>] GO USE [<DATABASENAME>] GO EXEC sp_addrolemember N'db_datareader', N'<DOMAINNAME>\<GROUPNAME>' GO USE [<DATABASENAME>] GO EXEC sp_addrolemember N'db_datawriter', N'<DOMAINNAME>\<GROUPNAME>' GO Change <DOMAINNAME>\<GROUPNAME> with your domainname and groupname Change <DATABASENAME> with your database name Without the < and > of course ;) "MarcH" wrote in message news:OAQrKQ31IHA.2068@TK2MSFTNGP05.phx.gbl... >I hope you can give me some details on the steps. > >> 1) Create a Windows AD group for all the users that need access to the >> object (table/view) and put all the users to that group > > I created a group (SQL users). > >> 2) Create a login on SQL Server based on the AD group you just created > > where can i create a login on SQL ? Is this a user refered to the group > (SQL users)? > >> 3) Create a user in the database based on the login you just created > > Where can i create that user? > >> 4) Give the appropiate rights on the objects for the user you just >> created. > > That i will find when i know where to create the user > >> 5) Now you can use Windows Authentication when connecting from the client >> to the SQL Server. > > >
!!! Thanks !!! for the detailed info. your the best !! thanks ! MarcH "Gé Brander" <ge brander microsoft> schreef in bericht news:0F8586E2-BB31-4915-A42B-46E7FA3F25DB@microsoft.com... > > "MarcH" wrote in message > news:OAQrKQ31IHA.2068@TK2MSFTNGP05.phx.gbl... >>I hope you can give me some details on the steps. >> >>> 1) Create a Windows AD group for all the users that need access to the >>> object (table/view) and put all the users to that group >> >> I created a group (SQL users). >> >>> 2) Create a login on SQL Server based on the AD group you just created >> >> where can i create a login on SQL ? Is this a user refered to the group >> (SQL users)? > > SQL Server Management Studio, connect to the SQL Server, click on the SQL > Server, click on '+' before 'Security', click with your right mouse button > on 'Logins' and choose 'New Login' and click on the 'Search...' button to > find your AD Group. > When the AD group is filled in, change the default database to the > database the group needs access to. Do NOT click on 'Ok' yet. >> >>> 3) Create a user in the database based on the login you just created >> >> Where can i create that user? > > Click on the right at 'User Mapping' and click on the database role > membership which the users need. (db_datareader & db_datawriter for change > permissions) and click on 'Script' on top of the window you see. (Why > script and not OK? Because now you can see what happens, and you can save > this script for later use when restoring the system if needed). Now click > on 'Execute' or hit 'F5'. The login and user is created. > >> >>> 4) Give the appropiate rights on the objects for the user you just >>> created. >> >> That i will find when i know where to create the user > Correct, you already have done it in step 3 ;) >> >>> 5) Now you can use Windows Authentication when connecting from the >>> client to the SQL Server. >> > Good luck! > > Gé >> >> >
