Για το πως ακριβώς λειτουργεί, ρίξε μια ματιά στο παράδειγμα εδώ: http://msdn2.microsoft.com/en-us/library/ms186271.aspx
Όταν έχεις ένα function που σου επιστρέφει αν ένας χρηστης ανήκει ή όχι σε ένα role, μπορείς να το χρησιμοποιήσεις οπουδήποτε σου κάνει. Μια πρακτική εφαρμογή περιγράφεται εδώ: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Το θέμα είναι ότι επειδή υπάρχει δεν σημαίνει απαραίτητα ότι πρέπει να την χρησιμοποιήσεις. Αν την χρησιμοποιήσεις, μεταφέρεις τμήμα της λογικής της λύσης σου στον SQL Server πράγμα που έχει τα μειονεκτήματά και τα πλεονεκτήματά του καθώς το ίδιο πράγμα μπορείς να κάνεις με κώδικα που θα ζει εκτός του server. Η απόφαση εξαρτάται από διάφορες παραμέτρους της σχεδίασης της εφαρμογής.
Vir prudens non contra ventum mingit