Re: Is_Member problem : Does user belong to custom Group

Uri,

Sorry for not using the right terms.
Here are the steps I followed :

In active directory:
1 - Created in Active directory a Windows group named 'MydomainName/MyApplReadOnly'
2 - Throwed in that group existing Windows users such as 'MydomainName/Ralph'

In sqlserver :
3 - Added in Security/Logins the windows Group 'MyDomainName/MyApplReadOnly' referred as type 'Windows Group' with Defaultdatabase 'Master'
4 - Added new user in Databases/MydatabaseName/Users choosing 'MyDomainName/MyApplReadOnly' as Login Name and the same (default) as User Name. Then configured the database Role Membership of this user by setting db_datareader and db_denydatawriter to true.

At this point, the statement
select is_member('MydomainName/MyApplReadOnly')
for the current user returns 0 although he is referred in active directory as someone belonging to the the windows group Mydomainname\MyapplReadOnly

5 - as you suggested, I created a role 'MyApplReadOnly' in Databases/MydatabaseName/Roles and added as role member the user MydomainName\MyApplReadOnly which is referring to the windows group with the same name.
statement is_member('MyApllReadOnly') still returns 0...

It seemed to me quite simple but maybe I'm doing something wrong...

By the way I could bypass this by querying directly Active directory but I have haven't a clue on how to do that. Is there a procedure to do that simply in VB.net?

Thanks for your patience, Uri.

Ralph








Uri Dimant a écrit :
Ralph

Let's make thing clearly. There is no "usergroup" in SQL Server. There is a login , there is a user that mapped to the Login
We can create a ROLE in the database and add the users.

Can you explain what did you do so far?



"Ralph BOSWELL" <rb-nospam-@xxxxxxxxxx> wrote in message news:uJPVdV5uGHA.4384@xxxxxxxxxxxxxxxxxxxxxxx
Uri,

for information, 'MydomainName\Appl_ReadOnly' would be able to login to sqlserver but with no rights to modify the data.

What I tested was to create a Role 'Appl_ReadOnly' to which I added the database usergroup 'MydomainName\Appl_ReadOnly' referred from Active Directory but the is_member('Appl_ReadOnly') still returns 0....
Do I need to add each individual logins ('MyDomainName\John') in the role (which would be in terms of maintenance quite annoying because the group exists in Active Directory...)

Ralph

Uri Dimant a écrit :
Ralph

Now I see what you meant

As I understood the 'MydomainName\Appl_ReadOnly' is not granted to login SQL Server , you would like to check if the login
'MyDomainName\John' does belong to 'MydomainName\Appl_ReadOnly' created in Active Directory , Am I right?

In that case you cannot verify that, unless you create a ROLE and add the login the ROLE




"Ralph BOSWELL" <rb-nospam-@xxxxxxxxxx> wrote in message news:%23Yl2L%234uGHA.4756@xxxxxxxxxxxxxxxxxxxxxxx
Uri,

Yes, indeed, it returns 0

Ralph

Uri Dimant a écrit :
Ralph

Does the statement return 0 ?




"Ralph BOSWELL" <rb-nospam-@xxxxxxxxxx> wrote in message news:epidEr4uGHA.4336@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

This should be an easy one...

Here is the case :
I've created some custom groups in Active Directory and put some existing individual users in it.

In SQL Server 2000, I referred the above custom groups as users for the database I want to use and configure some specific rights to each users group (db_datareader and db_denyDataWriter for a group called 'MydomainName\Appl_ReadOnly' for instance')

Based on a connection to the database using windows authentication (with the user logged to his computer with its individual login, let us say 'MyDomainName\John' which is belonging to the above 'MydomainName\Appl_ReadOnly' group), it seems that the following command : select IS_MEMBER('MydomainName\Appl_ReadOnly') always returns false.

IS_MEMBER seems to qury only the built-in roles and not custom roles/groups. Is this true?


What am I doing wrong? any solutions or examples?

Thanks a lot!

Ralph


.

Relevant Pages

  • Re: Is_Member problem : Does user belong to custom Group
    ... In active directory: 1 - Created in Active directory a Windows group named ... 'MyDomainName/MyApplReadOnly' as Login Name and the same as User ... We can create a ROLE in the database and add the users. ...
    (microsoft.public.sqlserver.security)
  • Re: Synchronize Active Directory with a SQL-Server database
    ... users of the Active Directory. ... on the login I check if the user exist in the AD too. ... username and the password on the database. ... So I need the record of the users in the database to have the same ...
    (microsoft.public.windows.server.scripting)
  • Re: Is_Member problem : Does user belong to custom Group
    ... There is no "usergroup" in SQL Server. ... login, there is a user that mapped to the Login ... We can create a ROLE in the database and add the users. ... I've created some custom groups in Active Directory and put some ...
    (microsoft.public.sqlserver.security)
  • Re: User Defined Database Role
    ... authentication - use a Windows Group for the login and generate a database ... If you are using SQL authentication you will ...
    (microsoft.public.sqlserver.security)
  • Authentication using Active Directory
    ... I am having a login form where username and ... password are stored in database. ... But i want to use the active directory ... to login into the project. ...
    (microsoft.public.vb.enterprise)