Identify Users with Content Encryption Enabled
Posted 07-09-2009 at 11:17 PM by hdawg
amadru @ BBF posted and then answered his own question about locating users with content protection enabled (after a smart call to T-Support).
Being inspired by the information provided, I decided to create a SQL script to give a list of users that have content protection enabled.
Here's amadru's info:
I'm not sure how / why / where databasename ='Yourdatabasename' is needed, so I've removed it from my query.
Here's a script to grab the SMTP addresses of all users with Content Protection enabled:
SELECT U.MailboxSMTPAddr FROM UserConfig U, SyncBackupRestore S
WHERE U.Id = S.UserConfigId AND UID='-133933004' and CAST(DATA AS VARBINARY(1000)) LIKE '%000005%'
Thanks to amadru, and I hope this helps someone else too!
Being inspired by the information provided, I decided to create a SQL script to give a list of users that have content protection enabled.
Here's amadru's info:
Quote:
got the answer from RIM for those that would like to know..
The following query can be used:
SELECT * FROM SyncBackupRestore
WHERE databasename ='Yourdatabasename' AND UID='-133933004'
If Content Protection is enabled you would see 000005 in the data portion of the record.
For example:
0x04000178 000000 000004 000005 000006 > Content Protection enabled
0x04000178 000000 000004 000006 > Content Protection disabled
From here you could create a query that would tie the UserConfigId to the SMTP address for each user.
The following query can be used:
SELECT * FROM SyncBackupRestore
WHERE databasename ='Yourdatabasename' AND UID='-133933004'
If Content Protection is enabled you would see 000005 in the data portion of the record.
For example:
0x04000178 000000 000004 000005 000006 > Content Protection enabled
0x04000178 000000 000004 000006 > Content Protection disabled
From here you could create a query that would tie the UserConfigId to the SMTP address for each user.
Here's a script to grab the SMTP addresses of all users with Content Protection enabled:
SELECT U.MailboxSMTPAddr FROM UserConfig U, SyncBackupRestore S
WHERE U.Id = S.UserConfigId AND UID='-133933004' and CAST(DATA AS VARBINARY(1000)) LIKE '%000005%'
Thanks to amadru, and I hope this helps someone else too!
Total Comments 2
Comments
-
Nice little tidbit for those of us that don't have this forced on the BES. It certainly can help assist with some of those less frequent and more thought provoking support situations (for lack of a better phrase, the "head scratchers").Posted 07-09-2009 at 11:43 PM by Otto
-
Hdawg,
What version of SQL are you using?
I see some folks over at Blackberryforums had issues, and so did I until I used the undocumented fn_varbintohexstr function, and also CHARINDEX instead of LIKE. Now it works like a charm.
I now have yet another column in my user data report with this check for Content Protection. ... I added 'Encryption Type' a few weeks ago as well from data from another post.
Now if we could get Memory Cleaning status....Posted 06-09-2010 at 01:10 AM by Sp1d3rM@n
