SQL Server Performance Forum – Threads Archive
SQL server 2000 windows authenticationWe have a active/passive cluster model.
We have a user base that needs access to the database.
Can we do this?
1) Create a local group , say test, on the primary(active, node1) node. Give access to that group(node1 est) to the database. Add users to that local group test1.
2) Fail over and create a local group on the passive node, node2, which is now active. Add that group(node2 est) to the database. Will that take care of everything. Meaning, no matter which node is active , using the virtual server, the users can access the database.
Please enlighten me.
You shouldn’t use local groups, use domain groups and it will work on all involved servers.
Argyle is right, however your solution will work.
I knwo , I should use domain groups. I am trying to convince some folks that it works, as they say it would not work. I knwo the recommended way is to use domain groups. But just wanted to make sure what I think works.
Argyle,Could you also please confirm,
If the password is the same it should work. Need to keep any password updates in sync in the future.
create a suitable role and then Map / Permit access to that NT User Group to that ROLE to access your db.
Hemantgiri S. Goswami
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami
Guys, you’re confusing the matter; Passwords are irrelevant the issue is that all the local group SIDs are granted login in SQL Server. Using roles is great but doesn’t address the question.
Argyle, The full picture should be: Domain user > Domain group > Local Group > Database role > Object Permissions You are doing the right thing, failing over your cluster to grant login to the other local groups. I’ve seen this work numerous times.
Why even go via the step of a local group? If you add the domain group instead to the role there is no need to add the local groups for all involved nodes in your cluster to the database.
I think we’ve both already advocated that and Jarris has agreed. There is a minor issue that you must use local groups to deploy policy to secure local resources such as file permissions, and this may be the factor that is in the mind of the policy maker. Domain groups works. Jarris’ method works.