[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Only allow Stored Procedure access to users in SQLServer 2005?

Posted on 2007-07-21
Medium Priority
Last Modified: 2010-10-05
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.

Question by:tradeline
  • 4

Expert Comment

ID: 19541372
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.

Expert Comment

ID: 19541380
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.

Author Comment

ID: 19543307

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?

Accepted Solution

kenhaley earned 2000 total points
ID: 19543754
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.  

Expert Comment

ID: 19543772
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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question