Security using Local Windows group

Question

Security using Local Windows group

rcagas-917601

Old Hand

Points: 378
More actions
November 18, 2008 at 8:00 am

#193876

I'm now venturing into SQL2005...
I have successfully set up security on a server with SQL2000. Basically, I created a local Windows group and added domain users. Then I created sql logins based on this group and granted access to a database.
I have installed SQL2005 instance on the same server and set up the Windows group in the same fashion, however users cannot connect to my SQL2005 db. What step am I missing? My steps:
- create local Windows group and add domain users
- in Management Studio, create the Login based on the Windows group with properties: Default Database = master, Server Roles = public, User Mapping = MyDatabase (public, db_datawriter), Grant permission to database engine, Login = enabled
- in MyDatabase -> Users -> myWindowsGroup -> Securables, I set up explicit permissions to tables in MyDatabase
I'm confused as to why users cannot connect. Did I miss a step? btw I am using Access 2003 as a front end. The tables are linked using a Trusted Connection.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Are you able to connect from a remote system using your account (I'm assuming you're running as a sysadmin level account)? The reason I ask is that Developer Edition for SQL Server 2005 is configured out of the box to only allow connections from the local server. It is also possible to put a Standard or Enterprise edition install in this state, too.

K. Brian Kelley
@kbriankelley

rcagas-917601 Old Hand Points: 378 More actions · Answer 2

Yes, I am able to connect from remote using my account. Surface Area Config is set up for remote and local connection. I am using the Enterprise edition. Thanks.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

If you grant their Windows user account a login directly (not through the group), do you see the same issue?

K. Brian Kelley
@kbriankelley

rcagas-917601 Old Hand Points: 378 More actions · Answer 4

Sorry for the late reply. I'm currently working on 3 projects and had to delay this one.

Now I have tried 1) adding the user to local Windows group, 2) adding the user directly as a Login, 3) creating a database role and then adding the user, 4) creating a database role and then adding the Windows group. None of these worked for me.

However, further tests revealed that the security settings do work on some computers but not in others. Now I am really puzzled. Any pointers to the direction I should be taking to troubleshoot this?

Thanks.

rcagas-917601 Old Hand Points: 378 More actions · Answer 5

I narrowed the problem to timeout issues. I can't open the linked table on the first try, but if I immediately try opening the table a second time, I can open it.