Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBgroup membership of a ADuser member of an ADgroup declared as DBuser Expand / Collapse
Author
Message
Posted Wednesday, July 8, 2009 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:38 AM
Points: 10, Visits: 82
Hi all.
We are migrating an application from SQL2000 to SQL2005 and changing the authentication mode from SQL to windows integrated.
The application accepts SSI credentials, but behaves wrong because need to know the role membership of the user to show the menus.
Seems to use sp_helpuser to know the DBrole for the DBuser,
select suser_name() returns the ADuser name but the ADuser being member of an ADgroup declared as DBuser returns nothing when using sp_helpuser.
If we give sp_helpuser the ADgroup name, returns the needed value in the GroupName column.

So, is there any way to know the ADgroup membership for a ADuser?

The application vendor wants me to declare manually each ADuser as login, DBuser and DBrole member!!!!

Any idea would be appreciated.
Post #749488
Posted Monday, July 13, 2009 7:24 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
With the nesting of AD groups I've seen flaky behavior using such methods. One workaround that we've used is to take the Windows groups and make them members of the appropriate database roles and then look against the database roles themselves.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #752021
Posted Monday, July 13, 2009 8:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:38 AM
Points: 10, Visits: 82
Hi Brian. thank you for your reply.

We have done that you suggest. The active directory groups are members of the database roles, but we only have the user credentials. How can we query the roles if we have not the name of the active directory group?
Post #752064
Posted Monday, July 13, 2009 9:39 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
Use the IS_MEMBER() function.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #752155
Posted Monday, July 13, 2009 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:38 AM
Points: 10, Visits: 82
Thank you, I'll try and tell you somethig about.
Post #752183
Posted Monday, July 13, 2009 11:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:38 AM
Points: 10, Visits: 82
Hi Brian.

IS_MEMBER() is like magic for my eyes!!

THEY will not have more excuse....

I'll try do someone to reprogram something to use it and cross my fingers to have the program modified instead do the things in the bad way.

Thanks a lot.
Post #752242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse