Is there a way to limit the resources available on a SQL Server based on a role or user. I would like to give priority to certain users and not others.
Not built in. You'd have to track who was connected, then either queue the requests (if dynamic sql) with a configurable priority, or a more brute force way would be to add a deny on any user that you're designating as having reduced access - just trap in the app and say the server is busy, try later. Both are clunky, admitedly!