• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Only allow Stored Procedure access to users in SQLServer 2005?

How do I ensure that a user account in sqlserver2005 only has the ability to execute stored procedures and select views?  The Schema/Role/User model is a bit confusing for me.

  • 4
1 Solution
You're right about the schema/user/role model being somewhat confusing.  Let me suggest an easy implmentation that should work unless you have unusual needs:
(1) Place all objects in the dbo schema.  This is automatically done if you create all objects logged in as the database owner (sa, for example).
(2) Create user roles so that each role is granted a specific set of permissions based on what the role is.  So, for example an AccountsReceivable role might have access to A/R related stored procedures and views but not to Payroll related procedures and views.  You can have as many roles as you like, so make them as finely granulated as possible.  If some A/R users can look at A/R aging but others can't, create two A/R roles--perhaps ARAdmins and ARClerks.
(3) To satisfy your requirement that a user only be allowed to use certain stored procs and views, only assign those objects' permissions to the role(s) that need them.  Do not assign any permissions on the tables (or other low level objects, such as triggers, etc.) to any role.  Remember that any member of the db_owner database role, or the sysadmin server role (such as sa) will have universal permissions on all objects in your database, so you don't need to grant those permissions.
(4) Finally, place users in the desired roles.  Do not grant rights or permissions to users--only to roles.

By doing it that way, you'll accomplish what you're trying to do; plus, you'll have a nicely manageable security framework for your database.
I see that I didn't make it clear that a user can be in multiple roles.  So if Susie needs to run A/R jobs as well as Payroll jobs, you can place her in both roles.  This way you don't need go through all the stored procs and views, deciding which ones to give to Susie.  Just put her in 2 roles and you're done.  Later, if you add an A/R view, you just grant permission to the AR role, and Susie (along with anyone else in the role) will pick up that permission.
tradelineAuthor Commented:

Thanks for the response.  That's the approach I've come up with so far, but I find it rather

 I simply want to create a public user that has access to ALL stored procedures and views in the database.  I don't want to have to grant access to these objects individually, because each time I add a new sproc or change the db schema, I have to remember to update the role as well.

So what I've come up with, is under the public role, instead of granting access to the individual objects, I grant access to the database in question (under securables), and then under explicit permissions, I grant access to Execute and Select, and that seems to do the trick.

The confusion lies in the fact that when I RETURN to the public role, under Securables, I don't see the selection(s) that I just made and saved.  They're blank.  The selections took hold though, because the web app now has access to the data.  Similarly, if I remove those items from securables, the web app loads blank pages.  It's not just securables that disappear.  The user's that I've added to the role also load blank, even though they have been successfully assigned to the role.  When I open up the db_owner role though, I see dbo as a role member.  But when I open up public, the role I added isn't there.  But it's taking hold as I said, because the app now has permissions.  Strange...

1.) Why wouldn't the current securables/users show when you load a user/role respectively?
2.) Is there a way I can assign these permissions to a schema, then assign the schema to the role?
The UI for maintaining security in SQL 2005 (SQL Mgmt Studio) is confusing.  When you click the "Securables" under any user or role, the window comes up blank, no matter what's been previously set up. It's sort of a "write only" interface.  You pick the objects you want to assign, and then do the assignment.  But it doesn't show you what you've done...at least not very nicely.  The interface sort of lulls you into thinking you can assign multiple objects' permissions to the role/schema you're working with in one fell swoop.  But I think you've discovered, that's not the case (at least, I didn't see how to do it).  You have to assign them one by one.  If you have many  to assign, you might want to write a T-SQL script to Grant the permissions using a cursor that goes through the objects of type 'stored proc' or 'view', etc.

To see what permissions have been assigned to a role called testrole (or whatever), use this query:
    sp_helprotect null,'testrole'

I think you're on the right track, except I think I'd use a different role than "public", but that will work fine for what you're describing.  

Stay away from schemas (for this purpose)...they're really designed to control OWNERSHIP of objects not permissions.  Prior to SQL 2005, there was no clear distinction between schemas and users.  Every object was owned by some user...now it's every object is owned by some schema.  A user or role can own schemas, but each schema can only have one owner.  If you decide in the future to implement a multi-role model (as I described before) you won't be able to expand this model to handle it.  And by making everybody an effective OWNER of the objects--you have no granularity of control.  Owners can modify the stored procs or delete them--you wouldn't want that.

For simplicity (and with no loss of flexibility) I've found that keeping all objects under ONE schema (namely, 'dbo') and controlling permissions at the role level works very nicely.

Stay on the course you're on, and use sp_helprotect to examine the current state of things.  
I just re-read your remarks about granting EXECUTE and SELECT at the database level.  I have to admit I've not tried that.  If that works, great.  But I don't know what sp_helprotect will show in that case, and maybe you don't care.  It would let users SELECT from any table though, wouldn't it?  Is that okay?  I have to admit this is a very simple approach--but it definitely lacks flexibility, because it can't grow to a multi-role system if that ever becomes necessary.  If you don't care about that, then this seems fine.

Good luck...whichever course you pick.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now