December 20, 2011 at 10:42 am
We have a purchased application database that we are trying to use for reporting using SQL 2008 and SSRS. The app stores Active Directory ID values for user ID's, but we have reporting requirements that we show the user name. I have successfully created queries that use a linked server to do AD lookups for individual users, but they rely on static values for the user ID. What I really need is a parameterized method to look up one user at a time, since our AD user list is quite large and contains more than the 1000 (roughly) row limit on AD queries using this provider.
Ideally, the solution wouldn't require any new objects to be created in the database, since it is subject to vendor software updates, etc. However, if it's required I can probably get exceptions made to create what I need, either in the vendor DB or somewhere else.
SELECT LegalGivenName+' '+LegalSurname as full_name
FROM OPENQUERY (ADSI
,'SELECT LegalGivenName, LegalSurname
FROM ''LDAP://xxx.xxx.xxx.com/OU=Users,OU=IT,OU=Xxx,OU=AdminUnits,DC=xxx,DC=xxx,DC=com''
WHERE name=''testuser''')
this code works, but to get it to run for any user, you must replace "testuser" with a variable, which is not allowed as an OPENQUERY argument. you have to go one step further and make the whole statement into a string and use EXEC or something similar to execute it dynamically (once you get the correct number of quote marks, which is no trivial task).
But once I was able to make it dynamic and use a variable user id, then I found myself at a loss for exactly how to implement this.
I tried to create a function, but you can't execute dynamic SQL in a function, and you can't pass a variable into OPENQUERY. I could create a stored procedure that uses an OUTPUT parameter to return the value, but then I can't call it from inside my main query in the report (since it's a stored proc and not a function).
One method that would work would be to create a stored procedure that would generate a dataset that includes the id and name of every user in the application - this is manageable because there are less than 100 users of the app, but it strikes me as not very scalable.
Any other ideas for the best way to design something like this? I suppose I could create the CLR stored procedures I've come across during my web searches, but are there any other straight T-SQL methods?
December 22, 2011 at 10:44 am
Based on the limitations you mentioned, I would suggest the following:
1. Create another database on the server to hold data you do not want to risk being deleted during vendor updates.
2. Within that database, create a users table with the data elements that you need for the report.
3. Create a stored procedure to query AD and populate the table as often as you think is prudent to catch any changes. To keep it simple consider truncating the table and repopulating it each time.
4. Change the report to retrieve the user information from this table.
We have to do things like this in our system and it works very well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply