Alerts for new login accounts

Hi all,
Looking for a method on sql server 2k of automated monitoring and
notification of creation of new login accts. When new login is created,
alert would go to one or more IT mgt people, perhaps also mgt of business
unit that use the database.
We use Blat (freeware email util) for sending emails & alerts. Situation
is we want to grant SecurityAdmin rights to one or two individuals (up to
now only sa acct/server admin accts have been used) to be able to review
sql error logs. Best I can tell, we have to grant them the Security Admin
role to do that through Enterprise Manager. However this also gives them
the right to create login accounts, so they could create their own login
acct, go in and browse around data they have no business looking at, then
delete the login account.
I have looked around, found that triggers are not supported on system
tables (idea was to create trigger on sysxlogins). Best I could find was a
method of using a scheduled sql agent job to capture data from the
sysprocesses table and record connections that are not in a "not" list to
the server event log:
http://www.databasejournal.com/feat...cle.php/2243271
thksIf you only want the users to be able to view the SQL Server
error logs, why not just grant them permissions to view the
log file at the OS level? The logs are just text files -
they can view them without using Enterprise Manager and
without having access to SQL Server.
If you did want to monitor for the addition of logins, with
SQL Server 2000 you can capture the addition of logins,
database users, adding logins/users to server/database
roles, etc using Profiler (or a server side trace). You can
find the events you can monitor in books online in the
topic: Security Audit Event Classes.
-Sue
On Tue, 1 Jun 2004 10:26:06 -0700, "GM"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>Looking for a method on sql server 2k of automated monitoring and
>notification of creation of new login accts. When new login is created,
>alert would go to one or more IT mgt people, perhaps also mgt of business
>unit that use the database.
>We use Blat (freeware email util) for sending emails & alerts. Situation
>is we want to grant SecurityAdmin rights to one or two individuals (up to
>now only sa acct/server admin accts have been used) to be able to review
>sql error logs. Best I can tell, we have to grant them the Security Admin
>role to do that through Enterprise Manager. However this also gives them
>the right to create login accounts, so they could create their own login
>acct, go in and browse around data they have no business looking at, then
>delete the login account.
>I have looked around, found that triggers are not supported on system
>tables (idea was to create trigger on sysxlogins). Best I could find was a
>method of using a scheduled sql agent job to capture data from the
>sysprocesses table and record connections that are not in a "not" list to
>the server event log:
>http://www.databasejournal.com/feat...cle.php/2243271
>thks

Aggregate where claus problem =/

Hi Guys,

Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity

Heres the query so far:

SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID

Thx for reading :-)

--PhilkillsTry this:
SELECT itemid, max(qsum)
FROM
(
SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS qsum
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
)
group by itemid|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

ah thx man perfect ^^

Although, is it not inefficient to have 2 sums for the same thing? or will SQL realise that their the same thing and count them as such..

Also would be it possible to change the query to select all Total's that are greater than say... 10..

like:

SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
WHERE SUM(ORDER_ITEM.ItemQuantity) > 10
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

although this doesn't work as it doesn't like that in the where clause =/|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

thx mate, lol seems kinda pointless "having" an extra keyword just to perform those extra operations ;p (that is instead of where)

aggregate calculations on xml ?

i've been breaking my head presenting aggregate summary calculations on
higher level nodes (. using sql server 2005 for xml path method. i'd
need output something like this:
<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt=1/>
<item id="i2" cnt=2/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt=1/>
<item id="i2" cnt=1/>
<item id="i3" cnt=2/>
</order>
<customer>
</row>
how to calculate values icnt and ocnt?
i know one way would be using translations. but i want to do it
directly from single query direct into xml. how to do it? is there a
way to reference xml elements from for xml path query and aggregate
that way? there must be an easy way. thanks in advance, Ed.Hello ubator@.gmail.com,

> how to calculate values icnt and ocnt?
Hows this?
declare @.x xml
set @.x = '<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt="1"/>
<item id="i2" cnt="2"/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt="1"/>
<item id="i2" cnt="1"/>
<item id="i3" cnt="2"/>
</order>
</customer>
</row>'
select @.x.value('count(/row/customer/order)','int') as ocnt
select @.x.value('sum(/row/customer/order/item/@.cnt)','int') as icnt
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Ubator,
You could try something like this :-)
USE Northwind;
WITH x AS (
SELECT
c.CustomerID
,(SELECT o.OrderID AS "@.id" FROM dbo.Orders AS o
WHERE o.CustomerID = c.CustomerID
FOR XML PATH('order'), TYPE
) AS orders
FROM dbo.Customers AS c
)
SELECT
x.CustomerID AS "@.id"
,x.orders.value('count(/order)', 'INT') AS ordercount
,x.orders AS "node()"
FROM x
FOR XML PATH('customer')
GO
HTH
/ Tobias|||Thanks, Tobias, this would be it. I thought there is a chance to submit
it in a single query with no need of procedure but i guess it's not
possible.
I've found also this link that could be useful:
http://www.codecomments.com/archive...6-1-746830.html|||> Thanks, Tobias, this would be it. I thought there is a chance to submit
> it in a single query with no need of procedure but i guess it's not
> possible.
How do you mean?
This is a single query...
/ Tobias