Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Alter DB from function?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 02/28/2003 :  18:17:05  Show Profile  Reply with Quote
I'm a bit stumped on a particular performance issue.

My application has a relatively complex method for assigning role based permissions that take into account several fields in the my Users table, including Department, Company, Base and others.

The query I use to determine if a user has permissions for a particular role is, while not terribly slow, not lighting fast either, but I use it absolutely everywhere, so even a moderately slow query won't be acceptable in the long run.

Therefore my desire is to cache a mapping of users to permissions based on various user defined patterns and rebuild that cache every time the Users table or the PermissionGroupEnrollment table changes.

The cache table would get quite large, so I don't really want to attach a trigger to Users and PermissionGroupEnrollment to rebuild the entire thing for a change on each row of either table, mostly because when the permissions table is updated from my .aspx page I'll update between two and a dozen rows, each update being a separate call to a stored procedure. To rebuild the cache table on each call would be, I think, extremely resource intensive.

Is there ANY way to trigger the rebuilding of my cache from a function? My desire is to rebuild the cache on the first access to dbo.HasPermission(User,Group) after a change has been made. I would have the Users and PermissionGroupEnrollment triggers set a dirty flag in my variables table and rebuild the cache only if that flag was set when my dbo.HasPermission function is called. But functions can't alter the database to rebuild the cache. Is there any sort of workaround to this?

Edited by - PlasticLizard on 02/28/2003 18:27:19

Most Valuable Yak

15732 Posts

Posted - 02/28/2003 :  18:29:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
Awww, why did you get rid of the extra info? It was very helpful.

My personal opinion, feel free to reject it: based on my experience with granting and denying permissions to stuff in a database, it's really not worth developing a system to do it when SQL Server already provides users, roles, and permissions on objects. It's really not that hard to utilize, and you can GUARANTEE that someone doesn't accidentally access something they're not supposed to. The only instance where you really need extra features would be row-level security, and even that can take advantage of existing users and roles in SQL Server. And you get all of this functionality for free, without needing to cache anything in the application.

If you are already incorporating your permissions groups into actual roles, then I have no argument. But you'll have to provide more details, like actual table structures, queries, and situations where performance is not to your liking. As of now I have no idea what the function actually does, and I'd think that there are other means to accomplish the same thing without needing functions to do them.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000