Re: Sql Server - Basic Permissions Question

You are making very good choices. A good security model includes adding all
users to both db_denyReader and db_denyWriter, and providing ALL data access
through Stored Procedures.

On cravat is to be sure that all tables and stored procedures are 'owned' by
the dbo schema. Make a practice of always adding 'dbo.' to the object name
in the create scripts. (e.g., CREATE PROCEDURE dbo.MyStoredProcedure)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"Ranginald" <davidwank@xxxxxxxxx> wrote in message
news:1156718450.101845.48600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a SQL Server database and I want a user to be able to execute
stored procedures only. These stored procedures use SELECT and WHERE
statements and return integers.

I have set EXECUTE permissions to the sp's as needed for the user.

It is possible to allow a user to have EXECUTE permissions on the sp's,
but deny everything else? E.g. If id did denyreader and allowed
EXECUTE on the sp's, would the command fail because the sp's have
SELECT and WHERE statements?

Thanks!
-Ranginald



.

Relevant Pages

  • Security: Accessing data in another db
    ... For all our ASP.Net applications we use a user called 'AppUser' to connect to ... which only has execute permissions on the stored procedures. ... The problem I am getting is when a stored procedure in one database needs to ...
    (microsoft.public.sqlserver.security)
  • Re: Least privilege to execute stored procedures
    ... In general is it better to create a role and assign the execute permissions ... be the only way they can execute the ddl stored procedures. ... In SQL 2005, you can grant EXECUTE permissions on database or schema ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Change the login in stored procedure
    ... while granting them EXECUTE permissions on your ... them permission to execute the stored procedures. ... > Is it possible to change the login in stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Whats the fastest way to assign role with all read/write/excute permissions?
    ... One option is to grant the role ... execute permissions on stored procedures, ... >to read/write all the tables/views and execuate all the ...
    (microsoft.public.sqlserver.security)
  • Re: permissions to some master SPs
    ... EXECUTE permissions will allow your users to use the stored procedures, ... Members of the sysadmin fixed server role or the db_owner fixed database ...
    (microsoft.public.sqlserver.security)