Group permission assigning
-
Hi,
I have a DB called Products on SQL server 2000 SP3. And a access front end on users computers that has ODBC linked tables.
Basic setup like, in my opnion, anyone would go for is to setup a group for read and a group for administration in AD. Then assign 1 group with DB_datareader (the reader group) and the admin group DB_dataread and DB_datawriter.
Ok So I created the reader group called DB-MO-Prodread and added all the domain users that require read access into ti. I then went into SQl server and added those users to the security->Logins giving them DB_reader to the Product data base.
Checked under databases->product->Users and I can see that group in there with the same permissions I set.
When i go to the client machine and try to add the ODBC source I cannot see the Product Database in the list for the "Change the Default DataBase to". And naturally I cant see it so I jsut get ODBC failed error if i try and run the access front end.
Has anyone had this situation arise?
Any help would be greatly appreciated
Time to make a change -
Are you able to see any of the databases other than master (such as tempdb)? Also, are you able to log on to SQL Server as said user with another tool, like Query Analyzer?
K. Brian Kelley
@kbriankelley -
Yes they can see other DB's but we have domain users set against some of the DB's for read access and this works fine, but as soon as I use groups it bombs and wont pickup the DB in the list!
Time to make a change -
ok sorry about that, stupid one probably. But it turns out after rebooting the computer they can then see the DB in the list to add as default in the ODBC wizard. I guess they need to reboot to request from AD what security groups they belong too then it updates and you can see the DB's newly assigned with access for u to see!
Time to make a change -
In Windows NT/2000/XP/2003, when a user logs on, a security token is generated for the user. This contains the information on the user's security group memberships. This token is only generated at log on. Therefore, if you change a group membership, for that change to reflect locally, the user has to log on and off in order to get a new security token with the new information.
SQL Server, though, should have checked group membership from a domain controller, though. However, the time it takes for domain controllers to replicate may have had an impact. Default when going across sites is 15 minutes. Default for intrasite replication depends on the version of Active Directory. In Windows 2000 it's 5 minutes but in Windows 2003 it's 30 seconds. If SQL Server was talking to a DC that hadn't yet been replicated the change, that would explain the issue.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply