Only allow Stored Procedure access to users in SQLServer 2005?

Posted on 2007-07-21
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
    LVL 6

    Expert Comment

    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.
    LVL 6

    Expert Comment

    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


    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?
    LVL 6

    Accepted Solution

    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 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 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.  
    LVL 6

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now