Query Optimization

Question regarding performance.

If I have a query such as:

Select UserId, Firstname,lastname from members where country='can'

If I were to call this from an .net executable as straight SQL to the Database vs. encapsulating the command in a stored procedure and calling the procedure.

What would be the performance differences? Would their be any issues (outside of security) that would make me choose to place the call in a Procedure?

Thanks

It probably doesn't matter in this case. Executing the SELECT statement using a parameterized command object will provide same benefit as calling SP with slighly more overhead. RPC execution for SP calls provide better performance than sending the entire SQL text. The answer is that it depends on your needs. Changing SQL statements embedded in applications is often harder than modifyin a SP. You can also take a look at the whitepaper below for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Query LDAP/ADSI for group members?

I am trying to get members of an Active Directory group by querying the AD server from Transact-SQL (SQL Server 2005). Although there does not seem to be any written list of LDAP attributes that can be queried in AD (or I am not finding it), I have gotten this far:

SELECT * FROM

OPENQUERY( MYSERVER,

'SELECT cn, msExchHomeServerName, userPrincipalName FROM ''LDAP://CN=Users,DC=MYSERVER,DC=COM'' WHERE userPrincipalName=''*'' ')

This gives me a user list. But I can't find the syntax or attribute name(s) to query in order to get the membership of a specific group - for example, the group "SQL_Developers".

Anybody out there familiar enough with LDAP, AD and OPENQUERY() to give me a hand?

Thanks....

Tom

SELECT Member FROM 'LDAP://CN=SqlDevelopers,OU=Container,DC=Myserver,DC=com'

returns an array.. use the distinguished name to reference your particular group.

|||Can't get that syntax to work - sorry. Not sure if it's a security problem (maybe) or if our servers are set up in a nonstandard way (possible) or if I'm just doing something wrong (likely). Can you elaborate a little bit more on your suggestion?

Query LDAP user group membership from SQL Server

this is driving me nuts!
all i want is a simple list of groups and members for each group in
Active Directory. I read numorous postings on this topic and no one
seems to know how to query it from sql server directly.
why can't Microsoft provide some kind of schema (views) for that?
i can issue a query like this
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''group''') b
but there is no relationship i can join between the two to connect the
dots between groups and users.
the problems i have is that i'm not a VB programmer, and i am not a
network admin and don't knwo how to use some of the vb code samples
provided in the newsgroup (see below). unless someone has a more
comprehesive link for how to set those scripts up.
---
To the best of my knowledge, you can retrieve MemberOf in
your list of attributes, but you cannot query on it. You
must return a recordset with memberof among the
attributes, then enumerate the recordset and look for the
info you need. MemberOf will be an array. Use:
'<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
(objectCategory=Person)(objectClass=user
))
;displayname, memberOf, objectCategory, cn,
adspath;subtree'
In your example, that means returning a recordset of all
users. If RS is the recordset, I code the following in
VBScript.
colMembers = RS.Fields("MemberOf")
For Each Item in colMembers
Wscript.Echo Item
---
either that, or does anyone knows how to script out that info from
Active Directory and output it to a text file for sql to pick up? I
just want a simple two column file to tell me all the groups and
members for each group. why would it be difficult?Hi
You need to look at the memberof attribute.
http://msdn.microsoft.com/library/d...ace_mapping.asp
This may also help
http://www.rlmueller.net/List%20User%20Groups.htm
John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1108757700.331401.193420@.o13g2000cwo.googlegroups.com...
> this is driving me nuts!
> all i want is a simple list of groups and members for each group in
> Active Directory. I read numorous postings on this topic and no one
> seems to know how to query it from sql server directly.
> why can't Microsoft provide some kind of schema (views) for that?
> i can issue a query like this
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''group''') b
> but there is no relationship i can join between the two to connect the
> dots between groups and users.
> the problems i have is that i'm not a VB programmer, and i am not a
> network admin and don't knwo how to use some of the vb code samples
> provided in the newsgroup (see below). unless someone has a more
> comprehesive link for how to set those scripts up.
> ---
> To the best of my knowledge, you can retrieve MemberOf in
> your list of attributes, but you cannot query on it. You
> must return a recordset with memberof among the
> attributes, then enumerate the recordset and look for the
> info you need. MemberOf will be an array. Use:
> '<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
> (objectCategory=Person)(objectClass=user
))
> ;displayname, memberOf, objectCategory, cn,
> adspath;subtree'
> In your example, that means returning a recordset of all
> users. If RS is the recordset, I code the following in
> VBScript.
> colMembers = RS.Fields("MemberOf")
> For Each Item in colMembers
> Wscript.Echo Item
> ---
> either that, or does anyone knows how to script out that info from
> Active Directory and output it to a text file for sql to pick up? I
> just want a simple two column file to tell me all the groups and
> members for each group. why would it be difficult?
>|||thanks for the reply. but i'm not trying to look up a window user
account's group info. i can see that in Active directory. what i need
is a way to script out all the groups and users info from AD and there
should a membership relationship like
user memberof
u1 grp1
u1 grp2
u1 grp3
u2 grp2...
and so on.
again, i'm not a vb programmer, so i need some intructions as to how to
run a script and so on. i have seen those links you posted before. i
couldnt' get them to work. something is missing from the instructions.
can anyone fill the gap?
thank you.|||Yeah John but have u been able to retrieve GROUPS from the Active
Diretory?
For example i want to get a USER and retrieve the GROUPS they belong to.
Any LUCK using OPEN QUERY?
*** Sent via Developersdex http://www.examnotes.net ***