Re: Newbie: Restrict user account access to stored procedures

From: BP Margolin (bpmargo@attglobal.net)
Date: 01/24/03 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Thu, 23 Jan 2003 23:38:52 -0500

Nathan,

You can deny users permissions to directly access tables and views by adding
them to the db_denydatareader and db_denydatawriter database roles.

SQL Server does not, to the best of my knowledge, support any database role,
or other technique, to automatically allow users execute permissions on
stored procedures. Personally, I like that as I have a modular style of
coding where one stored procedure calls another, and I only want a user to
have execute permission on the main stored procedure, not the dependent
stored procedures. I'm guessing that this is why SQL Server does not have a
database role that grants execute permissions on **all** stored procedures.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Nathan" <nahtan@email.com.nospam> wrote in message
news:b0qe9h$t68$1@lust.ihug.co.nz...
> I've got two accounts for my SQL Server database - the main one which I
use
> to add/edit/delete tables and stored procedures, and a dedicated user
> account for my website.
>
> I am manipulating all the data through stored procedures and would like to
> be able to restrict the account that is used by the website only to the
> stored procedures that I've created (none of the system stored procedures,
> or any of the tables directly).
>
> Is it possible to restrict a user account to the stored procedures only?
I
> know you can do it for each stored procedure individually, but is there a
> global setting? (I don't want to have to set the permissions every time I
> create a new stored procedure).
>
> TIA.
>
>
>
>

Relevant Pages

  • Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005
    ... access for the Visual Studio 2005, when working on ADO.NET (SQL Server ... Permissions are per logins and users. ... permissions can be granted through stored procedures, ...
    (comp.databases.ms-sqlserver)
  • Re: permissions required for executing CDOSys stored procedures
    ... properties of the sp_OACreate extended stored procedure has a permissions ... With SQL Server 2000 Enterprise ... Apparently only members of the sysadmin role can ... these stored procedures, however, the sql login for this application is ...
    (microsoft.public.sqlserver.security)
  • Re: Check SQL Server Table Permissions
    ... "BillyRogers" wrote in message ... program calls stored procedures in SQL Server from Access. ... permissions having been dropped for the group from one of the tables. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Securing Stored Procedures from being seen
    ... PERMISSIONS works for the current user, whoever he may be, and however his rights were granted. ... username, but in SQL Server, there are too many users to map an individuals ... granting the group execute permissions. ... see the names of any stored procedures to which he has no rights. ...
    (microsoft.public.sqlserver.security)