Searching Alerts of Users in SharePoint Portal Server 2003 DBs

Today I was working for a user request who has received some alerts which are around 1.5 years old.  This happened due to some problem with the SPS topology of our client which we fixed yesterday so we were expecting some alerts gone to users so I was trying to search where can I go and delete the alerts for the user.  You will be amazed to know that there is no place in SharePoint Portal Server 2003 to find a specific alert and turn it off.  So I decided to switch to DB for that.  here are the steps to find the Alert

1. Get UserGUID by using Preferred name from UserProfie Table in Profile DB and copy it.

SELECT     *
FROM         UserProfile
WHERE     (NTName = ‘Domain\UserName’)

2. Get Alerts of Users by using UserSubscriptions Tables of Serv Database using the following query

SELECT *, userId AS UserGUID
FROM sub_UserSubscription
WHERE (userId = ‘{A8E9B020-BD66-4FD3-8826-6002DAE3FCE1}’)

you will get the alerts for the user.

3. Now if you wanted to confirm the alerts by checking it site, alert type and other details simple use a Inner Join to Table Subscription Details and check the Subscription ID in both the tables.  You will see the details in the rows.

SELECT *, sub_UserSubscription.userId AS Expr1, sub_SubscriptionDetails.subscriptionId AS Expr2
FROM sub_UserSubscription INNER JOIN
sub_SubscriptionDetails ON sub_UserSubscription.subscriptionId = sub_SubscriptionDetails.subscriptionId
WHERE (sub_UserSubscription.userId = ‘{A8E9B020-BD66-4FD3-8826-6002DAE3FCE1}’)

Execute the query and the result will be given below.

Enjoy!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s