How stable/usable is SqlRoleProvider & aspnet_regsql?

Question

Hi All, this isn't asp.net specific, some of these apps are WinForms, also will be adding some Wpf and possibly some silverlight apps in the future.

I’ve a requirement to put role & permissions based access security into a new app we’re developing here.

The .net RoleProviders in general, and SqlRoleProvider in particular seem to have everything we need, however I’ve a few concerns around the Db Schema changes aspnet_regsql needs.

I’ve tried googling for these but I’m not sure what to ask for.

i.e.

1. How straightforward (or not) is it going to be to upgrade this between .net versions?
2. Will .Net 2.0 and .net 3.5 apps happily use the same schemas? i.e. Can we have .net 2.0 and 3.5 apps use the same permissions in the same Db?
3. How stable are the Sql Security providers, is anyone out there using them in anger? Are they worth using?

Am I better off biting the bullet and implementing my own - limited - Role functionality (that will do everything I need) and my own custom RoleProvider?

As you can see I'm caught between "not wanting to re-invent the wheel" and "not getting stuck with problems with some unknown 3rd party code" (unknown to me anyway, that's why I'm asking you)

Thanks in advance for your advice & testimonials,

BW

Answer 1

I've been using the built-in providers for years now (on different projects) with no issues. Nothing has changed since it was roled out for 2.0.

If the sql provider does everything you need, go with it, there's little reason to roll out your own unless you need additional functionality.

The one thing you can keep your eye on - and this applies to profile, membership and roles - is the efficiency of the sprocs. If you look at the sprocs you'll see that there is activity around getting data from the various tables on every execution. For example, almost every query (that I remember looking at), queries for the appid for the current request. If you only have one app running on the DB that may be overkill and can be changed. This article (The Code Project) by Omar Al Zabir talks about profile improvements.

Answer 2

I believe the SqlRoleProvider is the way to go. It's highly scalable and easy to set up. Unless you've already rolled your own proven solution (then why are you posting this question) then this is a no brainer.

I agree with webchanix, the system does allow for multiple applications and does lots of table scans but in the end, I think we're talking about an pretty small perf hit based on it's benefits.