How to get the OU information on a usr into my SQL VIew

Hi
I am reading AD Users, who are organised in different OU in my AD.
In the OU i have some organisational information that i would like to link
together
with my AD Users when i make my SQL view.
How can i join these 2 information in my SQL View ?Could you please provide schema, data and expected results.
Thanks
Peter
"sdane" wrote:

> Hi
> I am reading AD Users, who are organised in different OU in my AD.
> In the OU i have some organisational information that i would like to link
> together
> with my AD Users when i make my SQL view.
> How can i join these 2 information in my SQL View ?|||sure thing,
I have a domain int.dem.local
here i have a OU called Finance Department. On the OU there are info like
City=Roskilde and State/Proviens=2. Floor.
In the OU i have some users with the normal fields filled out like first
name, last
name, User Logon name.
In the AD these information is kept in 2 places. I would like to have a SQL
View
that shows me records like
first name, last name, User Logon name, (OU) City, (OU) State/provins
This way i can maintaine the generel information about my users location a
single
place and still have all the information in one record in my sql.
Is this possible ?
Is this want you were asking for ?
"Peter Nolan" wrote:
> Could you please provide schema, data and expected results.
> Thanks
> Peter
> "sdane" wrote:
>|||Apologies for not getting back sooner, minor problem to sort out.
I think we are talking cross purposes here. For any view to work it needs to
be in a SQL Table with data in it, I do not think it can be otherwise unless
its part of a linked server.
So could you provide some table structure i.e.what it looks like in SQL
Server tables, and possibly some play data.
Peter
"sdane" wrote:
> sure thing,
> I have a domain int.dem.local
> here i have a OU called Finance Department. On the OU there are info like
> City=Roskilde and State/Proviens=2. Floor.
> In the OU i have some users with the normal fields filled out like first
> name, last
> name, User Logon name.
> In the AD these information is kept in 2 places. I would like to have a SQ
L
> View
> that shows me records like
> first name, last name, User Logon name, (OU) City, (OU) State/provins
> This way i can maintaine the generel information about my users location a
> single
> place and still have all the information in one record in my sql.
> Is this possible ?
> Is this want you were asking for ?
> "Peter Nolan" wrote:
>|||I am using ADSI til make a linked server to connect to my active directory.
And then i have a SQLView to get the data from a user in the AD.
The AD user info is
givenname = Hans
sn = Jensen
SAMAccountname = dom.hans
This user is located in a Organisational Unit (OU) in my AD. On this OU ther
e
are info like
OU = Brugere
City = Copenhagen
State/Provinse = 2.Floor
What i want is to make a SQL View that can give me records were these two
pieces of info is joined into one record like
givenName sn SAMAccountname City
State/Provinse
----
--
Hans Jensen dom.hans Copenhagen 2.Floor
I made my Linked ser with these sql scripts:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject',
'DomServer'
sp_addlinkedsrvlogin ADSI, false, 'sa', 'AD_Reader', 'Reader'
After this a make the SQL view using this sql script:
create view v_ADSI(SAMAccountName, givenname, sn)as
SELECT * FROM OpenQuery(
ADSI_INST,'<LDAP://ou=Brugere,dc=lpb,dc=local>;(&(objectCategory=Person)(obj
ectClass=user));SAMAccountName, givenname,sn;subtree')
This gives me the ad users info, but have do i get the OU info and link it
with the
user info.
"Peter Nolan" skrev:
> Apologies for not getting back sooner, minor problem to sort out.
> I think we are talking cross purposes here. For any view to work it needs
to
> be in a SQL Table with data in it, I do not think it can be otherwise unle
ss
> its part of a linked server.
> So could you provide some table structure i.e.what it looks like in SQL
> Server tables, and possibly some play data.
> Peter
> "sdane" wrote:
>

How to get the folder wise security info of all the users?

Hi,

The Report Manager portal has many folders. For each folder there are specific users with different roles.

I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID

The above query fetches all the users and their roles.

The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.

TIA

The security settings appear to be in XML. See the GetPolicy stored procedure.

select xmldescription from secdata

You can see the security on each folder by running rsscripter utility and looking at the resulting code in the fldr.rss files.

'Set Folder security
Dim pol(4) As [Policy]
Dim sersec As Serialization.XmlSerializer = New Serialization.XmlSerializer(GetType(Policy))
Dim tsec0 As New StringReader("<?xml version=""1.0"" encoding=""utf-16""?><Policy xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""> <GroupUserName xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">BUILTIN\Administrators</GroupUserName> <Roles xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices""> <Role> <Name>Content Manager</Name> </Role> </Roles></Policy>")

If I was trying to reverse-engineer how to get the security roles, I would look into SQL Profiler to see what calls are being made when setting/getting security on folders.

You may have more luck using SOAP & Web Service to get the information you want.

regards,

Andrew

How to get the current User when using a general connection user

I have a VB.Net app and a SQL Server 2005 database. Users must login to use
the application, and I have an Employee table to store their details.
However, I use a common user ID to connect to the database (for reasons I
won't go into here).
My problem is, some of my triggers need to know who the current user is, and
of course I can't use the current connection information to get this as I
always get the common user ID.
Does anyone have a technique to solve this dilema? Maybe some way to set a
variable or something when I connect so that I can determine which user has
initiated the connection?You can use application roles to achieve that. Works something like this:
a) Every user has a Windows account using Windows Authentication
b) When they run the application you enable the application role
c) Inside the application's session you can look at what the username is,
including in triggers, using suser_sname()
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"David" wrote:

> I have a VB.Net app and a SQL Server 2005 database. Users must login to us
e
> the application, and I have an Employee table to store their details.
> However, I use a common user ID to connect to the database (for reasons I
> won't go into here).
> My problem is, some of my triggers need to know who the current user is, a
nd
> of course I can't use the current connection information to get this as I
> always get the common user ID.
> Does anyone have a technique to solve this dilema? Maybe some way to set a
> variable or something when I connect so that I can determine which user ha
s
> initiated the connection?
>

How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?

How to get statistics on DML statements per table

Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.

Rgds

Bob

There is no easy way to obtain this information. You could also take a look at 3rd party tools that can get this sort of information from the transaction logs.

How to get Full-Text to not ingore special characters

We just implemented a full-text index on our product master table,
however the users are now screaming because they cannot search on some
of the special characters that are commonly found in our product
descriptions, specifically the #, %, and period (.)

These characters are not in the Noise file, so no luck in just
deleting them from there, but somehow, the full-text is automatically
ignoring those characters, and we would like for the full-text to not
ignore these characters.

Any insight or help would be appreciated.

Thanks"dotnetprogrammer" <tim_60173@.yahoo.com> wrote in message
news:e856daff.0309050524.703c112@.posting.google.co m...
> We just implemented a full-text index on our product master table,
> however the users are now screaming because they cannot search on some
> of the special characters that are commonly found in our product
> descriptions, specifically the #, %, and period (.)
> These characters are not in the Noise file, so no luck in just
> deleting them from there, but somehow, the full-text is automatically
> ignoring those characters, and we would like for the full-text to not
> ignore these characters.

You're probably better off asking in
news:microsoft.public.sqlserver.fulltext

The experts there can help you.

Off the top of my head, I don't believe you can search on those, but they
will be able to tell you for sure.

> Any insight or help would be appreciated.
> Thanks