Get active directory users from SQL Query Analyser

Get active directory users from SQL Query Analyser
--
I am trying to do a select in SQL Query Analyser to get the list of users in
active directory.
I use the following code:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
But appears the following message error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
What could be?
Tks.Try moving the ORDER BY clause outside of OPENQUERY. There is also something
a little strange in the outside SELECT; maybe a typo?
SELECT [Name],SN -- Why was this here? :[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
ORDER BY Name
--
"Rui Oliveira" wrote:

> Get active directory users from SQL Query Analyser
> --
> I am trying to do a select in SQL Query Analyser to get the list of users
in
> active directory.
> I use the following code:
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> SELECT [Name],SN[Last Name]
> FROM OPENQUERY( ADSI,
> 'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
> DC=domainname,DC=com''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
> name')
> But appears the following message error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> What could be?
> Tks.
>

Get a list of the objects owned by a users

Hi,
My question may be stupid, but can;t figure out a way to do it "simply".
I'm taking over the admin of some SQL instance and I want to clenu up the
logins list. But before deleting anything, I need to know what objects are
owned by the user I want to delete to not brak anything.
Is there any way (not matter how) to retrieve the exclusive list of object
owned by a user ?
thanks,
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !Try this
SELECT o.name
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE u.name = 'username'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:%23Q8GcImHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hi,
> My question may be stupid, but can;t figure out a way to do it "simply".
> I'm taking over the admin of some SQL instance and I want to clenu up the
> logins list. But before deleting anything, I need to know what objects are
> owned by the user I want to delete to not brak anything.
> Is there any way (not matter how) to retrieve the exclusive list of object
> owned by a user ?
> thanks,
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
>

get a count between a time range

I am trying to figure out if I can do this in a SQL query. I need to
know how many users are active during a given time period. I want to
be able to return the results below. If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3
Start Time End Time User Id Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
I am pretty much stumped as how to proceed.
Thanks TimAssuming you're trying to query users who were active for any part of
the duration @.StartTime...@.EndTime inclusive.
SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.
Thanks Tim
Lubdha Khandelwal wrote:
> Assuming you're trying to query users who were active for any part of
> the duration @.StartTime...@.EndTime inclusive.
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
> OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||I'm . Do you want to count all the unique users that were
active during each of the active duration for all the users?
If so, this could get you that count:
SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId|||TDT (tim.trujillo@.gmd.com) writes:
> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below. If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
> Start Time End Time User Id Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
> I am pretty much stumped as how to proceed.
I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.
CREATE TABLE [dbo].[UserActivity]
(
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[UserID] [varchar](50) NULL
)
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
Erland Sommarskog wrote:
> TDT (tim.trujillo@.gmd.com) writes:
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested query.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for providing the table and data.
SELECT A.StartTime,
A.EndTime,
A.UserID,
count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
ON A.StartTime between B.StartTime and B.EndTime
OR A.EndTime between B.StartTime and B.EndTime
or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
A.EndTime,
A.UserID
ORDER BY 1, 2, 3
Roy Harvey
Beacon Falls, CT
On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.trujillo@.gmd.com> wrote:
>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>CREATE TABLE [dbo].[UserActivity]
>(
> [StartTime] [datetime] NULL,
> [EndTime] [datetime] NULL,
> [UserID] [varchar](50) NULL
> )
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>Erland Sommarskog wrote:|||This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?
CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))
Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.
SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;
This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.

get @@trancount for all users/ connections URGENT

Hi all,

Is there any way to get the @.@.trancount for a connection from outside the connection?

The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.

So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)

So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.

Thanks in advance,

Gert-Jan

In 2005, you can use this query to see that information.

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

|||The DB is on 2K, do you have this script for 2K?|||

Yeah, it is in sysprocesses. The column is open_tran

select spid, open_tran

from master..sysprocesses

|||

Louis,

Thanks for the very quick response, i'll get back to you if you saved the day.

Regards Gert-Jan

|||

Hi Gert-Jan van der Kamp,

> So another theory is that a certain sproc began a transaction, but never finished because of an error.

> (We had some strange timeouts as well, so this is quite plausible.)

I think you are looking for open transactions. It that case, the "select" statement provided by Louis (hope we can have your new book about DMVs and DMFs soon), will not help you much because that session will not have a match in sys.dm_exec_requests. You can find an example in BOL, under the topic about "sys.dm_exec_sessions".

This is from BOL.

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

Forgot to mention that in SS 2000 your choice is to use "dbcc opentran".AMB

sql

geographic load balancing

Say we have 2 farms of servers in Asia and America.. what hardware or
software is out there that load balances geographically i.e Asia users go to
the Asia farm and America users go to America farm as an example ? Actually
how are IPs ranged for different countries so that any of the load balancing
tools know that this IP is from this country ,so and so forth.. Any article
that talks about IP addressing for countries would help
I know this is not SQL related question but SQL is in the farm and not too
sure whom to askYou cannot load balance SQL Server unless you are doing
read only servers.
If you are talking Web servers and such, many products
could potentially do it from HW to SW. If you want to do
it on Web servers, I'd ask in an IIS or Windows forum.

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale
Use sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas

"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generate Exchange Task from T-SQL

Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.

Here is an example:

A client must receive paperwork every 6 months based on a date stored in our SQL database.

2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.

Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.

We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!

Thank you - Jeremy

Best is to use CDO (Colloborative Data Objects) or Outlook Object Data Model outside of the database. You could write code using sp_OA* SPs but it is not going to be a robust implementation. You can use a SQLAgent job with ActiveXScript task to do the task creation. See below links for more details on how to use CDO and Outlook object model.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp

http://msdn2.microsoft.com/en-us/library/ms268893.aspx

There are lots of KB articles that contains code for using CDO / Outlook Object Model to create messages, appointments, items, tasks etc.