microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Looking to subscribe "roles" rather than "users"

Subject: Re: Looking to subscribe "roles" rather than "users"
From: matt roberts
Date: Mon, 20 Mar 2006 13:51:33 +0000 UTC
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hello Joe & CoreyB,

You've both been a greta help - thanks for the info. I'll going to mull it over and I'll let you know what the solution is.


Going back to one of your original ideas, you could also create a
dummy subscription for each role and a custom delivery protocol that
uses SMTP. Then the custom delivery protocol could query the database
for role membership.

I think that maybe a bit easier to implement. Plus all notification
logic remains in the SQLNS side of things.

My $0.02 worth...

Joe

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

On 10 Mar 2006 09:01:05 -0800, "CoreyB" <[email protected]> wrote:

Or slight modification - if you have all the users in one column
(separated by commas, as you mentioned) - you might have to use..

event.UserIDs LIKE '%' + subscriptions.UserID + '%'

right??  This will match any try to match your comma-separated list
with a specific subscription userid.  If you comma-separated values
are all in one column called userids, then you'll have to use LIKE to
match on those substrings.

matt roberts wrote:

Hello Tony,

Thanks for the suggestions.

I understand the second option you suggested - about setting up
subscriptions that represent the role memberships. Like you said
there's overhead involved in maintaining this but its possible.

I've got an idea that is based on your first suggestion, although
doesn't
involve sending one event for each member.
My idea is this (and I've not yet prototyped it, so I can't
guarantee I haven't
missed anything);
1) All users in my application subscribe to recieve notifications
about new
records, for their userID.
2) When a new record is added, an event is created that includes the
recordID,
and a comma-seperated list of users who should get the notification.
3) The subscription matches on subscriptions.UserID IN event.UserIDs
And thats it!

I *think* its going to work, if you can spot any holes in this then
feel free to flame me ;)

Once more, thanks to everyone for the advice - its very appreciated
:)

Another option would be to use the data you know about  the users
and the roles to resolve the role to each of the current members in
that role.  For each of the members generate events for each of the
individual subscribers which are already known to NS.  This may be
a large number of events in some cases depending on your role
membership, but the control stays within your application and no
changes required to what is carried with NS.

Another option still is to set up a number of subscriptions which
would represent the role memberships for the users - this would
involve a greater amount of subscription management as users
changes the roles they play, but could be work as well.  It would
drop the number of events coming through to one containing say the
RoleID and all subscrbers to a role with id = # would then recieve
it.  There may be limitations here to how customized the mail
itself could be based on receipient.  Did something quite similar
to this with account data where an event would come through for the
account and all subscribers to events for that account would get a
copy of the mail (same mail to each subscriber).

HTH
--Tony
"matt roberts" wrote:
Hello Joe & CoreyB,

Thanks for the reply.

I like the simplicity of the exhange idea,
but........unfourtunately creating exchange distribution lists
won't work for this app. The reason is that its a web app that we
provide to many customers, who install it onn their intranet. We
have no control over these intranets, and cannot setup
distribution lists on any of them. Also, the "roles" change quite
often, as people come and go in the application, so it create a
big overhead to sync the exhchange distributions with them.

Another idea I am considering is to get my web app to pass all the
emails
in the event when it passes one to SSNS. That way, I could allow
all
users
to subscribe to the notification, and then only send out
notifications when:
"subsriber.emailaddress IN event.allemailaddresses"....
Does that sound feasible?
Thanks!
Matt.
Hi Matt -

Thanks for the kind words regarding a previous post of mine!   :)

CoreyB's suggestion of creating an Exchange distribution list for
your roles would be the easiest solution to implement. That way
the subscriber and associated subscriptions would accurately
reflect what you want to have happen.

Will that work for you?

HTH...

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On 8 Mar 2006 08:53:41 -0800, "CoreyB" <[email protected]>
wrote:
My suggestion would be to tack on something in your mail system
(exchange, groupwise, etc. whatever you're using) to have these
"roles" existing as email lists.  User1, User2, User3 could be
grouped in EmailList1 in exchange.  Then in notification
services, the subscriber device would be the email address for
EmailList1. This would be easier than doing a custom delivery
protocol.

Or maybe if you don't have exchange or similar, you can setup
forwarders on your email server to forward all messages to
EmailList1 to User1, User2, User3.

matt roberts wrote:

Hi all,

I'm starting designing a SSNS application to support our own
web based app. I've just got the Shyam book for SSNS 2005,
which looks pretty good so far. Anyway, I have a specific
requirement that is making me nervous, and I would appreciate
any responses:

In our web app, we have users and roles. A role is a group of
users, for example "Data Inputters", or "Auditors". As part of
the SSNS solution, I need to be able to "subscribe" roles as
well as users. So for example all "Auditors" want to be
notified when a record appears in table X.

I've not seen any examples of this in the book or in the books
online. In mulling over how this could be done, so far I can
only think of the following solution:

1) Subscribe a "dummy" user, (my role)
2) Create a custom delivery protocol, that takes notifications
destined for
my role subscriber, and instead passes the notification on to
all
users who
are members of that role.
But this solution "feels" wrong to me, quite complex to develop
and
maybe even not possible.
I've looked through the existing posts here, and a few posts
seem
to be quite similar but don't seem to provide a way forward. A
very helpful chap called Joe Webb wrote this in response to a
similar posting:
That's true. SQLNS is a develoment framework so you cannot
really pick

and choose what portions you'd like to implement.

However, in many (or most) cases you can pretty easily
integrate SQLNS

into your existing process with little duplication.

For instance, since you already have your data in a specific
table, you

can use that information as the basis for your events.

So I'm wondering if this means that since I already have and
maintain a table
of users, and details about which users live in which roles,
then
somehow
I can tell SSNS to use this data....or perhaps I got that
wrong.
Any thoughts/opinions appreciated!
Matt Roberts
http://www.geekswithblogs.net/mattrobertsblog


<Prev in Thread] Current Thread [Next in Thread>