sql server access through group membership

I am using SQL SERVER 2000 and trying to give a login access to a user through windows authentication. When I was noticing some other logins properties, I See at one place security access through group membership option under grant access and deny access options. surprisingly if I try to create a new login I see only the grant and deny access options but not "through group membership". Could somebody please tell me how can I enable this option for a new login??I would really appreciate if someone could email me the solution at manish)hf@yahoo.com.
Your time and information could be greatly appreciated.

thanks

Where did you see "Through group membership" option?

You can grant login to a sql server to a windows group or login in the same way.

The WITH GRANT option is available only on object permissions, not logins themselves.

And that option is not available via Enterprise Manager. You have to actually execute the T-SQL command.

Look up "GRANT, WITH GRANT option" in BOL for more info.


Now as far as granting someone access through group membership, all you have to do is give the Windows group access and then add the member to the group via the normal Windows method. You don't even need to add the user to the SQL Server.

can somebody look into this word document and tell me how I can get the option of "through group membership" option while creating the new login access to sql server.

thanks,
Manish

Well, there isn't a Word document attached. But from looking at the very hard to read shrunken image that was attached, it appears that this is either the Microsoft SQL Web Data Administrator or this is accessing SQL Server through the web.

Is that correct?

I opened the properties of the sql server login and then printed that screen. Initially I thought to paste it on word doc but submission only accepts jpegs or bmps so I posted only the jpeg. Could you please copy that image to a word doc and increase the size to see what I am saying about!!!
there is no web administrator or accessing sql through web..i am just talking about just new sql server login properties.

thanks,
Manish

Hey Guys, I am sorry- the image is still not clear.
http://techrepublic.com.com/5100-6329_11-1061781-2.html

go to this URL and look at the very first image--which says ::sql server properties-NEW LOGIN".. in this dialogue box ---for windows authenticaiton you can see GRANT ACCESS and DENY ACCESS under SECURITY ACCESS. What I am saying is for one of my users I also have a third option which says "through group membership".
How to get that specific option..when I tried to create new login for windows user or group I don't see such an option.
I also attched the picture I am talking about with only grant access and deny access options.

thanks,
Manish

I can't duplicate this behavior nor can I find anything that says that it is possible. Were you doing this in Enterprise Manager? Is your machine in mixed mode or Windows authentication mode?

This is an old thread, but I ran into it now and found out that you get users added with "Via Group Membership" if you grant privilege to a windows account that does not exist in the database. For example if you have a windows account [domain1\user1] then doing

grant execute on myproc to [domain1\user1]

will create this user. But this user can't execute the procedure without creating proper userid for it in the database.

There are two spots where "Through group membership" may appear:
as "Server Access", if you run "sp_defaultdb 'domain\user', database" without having run sp_grantlogin previously,
and as "Database Access" if you run "grant select on table to [domain\user]" without having run sp_adduser previously.

You guys mean to tell me that you can't give users access to SQL DB, Sp, Tables and what not thru group memebership?

So I have 3000 users in our company that uses DB on SQL server, you are telling me that I have to create 3000 Ad Ids into sql? If that what you guys are saying, it wrong. If thats the case why would MS let you add Ad Groups in SQL logins?

I suppose every one knows this, but lets say I add an AD group, called OPS which has 3000 ad memebers. Then I go ahead and give access to a DB to the Ad group Ops.

and assigning rights to various objects should do the trick for all the 3000 users. Those users would show up as Members via Group Membership.

I didn't see anyone said you can't grant permission to ad group. As they said you can create sql login for ad group then grant permissions, but can't grant permission to individual ad account that is member of that ad group unless you create sql login for the account.

Once you grant permission to ad group that has sql login, all members of the group have same permission. You can see member list in AD but those members are not in sql server.