CODEWITHSUNDEEP
sql-serveractive-directory-group
david wendelken
david wendelken

Reputation: 291

How to get nested Active Directory groups for a user in a sql server table function?

I want to build a sql server table function that accepts a user name as its parameter and returns a table of all active directory security groups that user is a member of.

I know how to write a query to get the user using the parameter.

I know how to write a query to get the nested groups for that user.

I know how to write a table function.

I cannot find a way to do all that in one table function.

I can do it in a stored procedure, but not in a table function.

Anyone know how to put it all together?

Upvotes: 0

Views: 502

Answers (1)

david wendelken
david wendelken

Reputation: 291

Found a defect in the ldap query, so I'm still working on that.

However, I found a way to get an ldap query useable in a table function when I need variables in it, which is what I was asking about.

First of all, this setting needs to be set:

exec sp_serveroption your_server_name, 'Data Access', true;

Then you write a stored procedure that does all the things your table function is not allowed to do. In my case, it checks the original_login() value and issues the ldap queries to return a result set. The result set composed of multiple records, one per active directory group, in a single varchar field.

Then you write a table function that calls the stored procedure:

create function mytablefunction returns table as return ( select * from openquery(your_server_name,'exec your_stored_procedure withresult sets (([your_field_name] nvarchar(512))'); )

Don't bother trying to figure out a way to make that exec string into a variable. You can't do it in this context.

Upvotes: 0

Related Questions