Re: Stored Procedure Ignoring Table Permissions

Hi
I assumed this user is not a memebr of sysadmin or db_owner roles

use demo
--deny permission on table test to the user 'Myuser'

execute as user= 'myUser'

delete from test

--The DELETE permission was denied on the object 'test',

-- database 'demo', schema 'dbo'.

revert

go

--create a sp that does deletion

create procedure dbo.myp

as

delete from test

--

grant execute ON object::dbo.myp

TO myUser;

execute as user= 'myUser'

exec dbo.myp --worked fine

revert

<dschruth@xxxxxxxxx> wrote in message
news:1185318017.951150.323780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I've been bashing my head against this one all day:

We have a MS T-SQL 2005 database with ~12 tables and ~45 stored
procedures (all created and owned by myself under a dbo account).
The access front end calls these stored procedures and everything
works well ... but a little too well... It seems that no mater what
permissions I set on the tables, the stored procedures do anything and
everything that is written in the code... regardless of which domain
user is logged in to windows and using the access font end.

The main example of this problem is a domain user called "NTreader"
who is a member of a group called "NTreaders". the "readers" group
has a corresponding group on the sql server as "SQLreader" and this is
a member of a role called "SQLreaders" . I have explicitly denyed
"SQLreaders" deletion permissions on "TableA". But when "NTreader"
runs stored procedure "spDeleteA", it runs and deletes flawlessly.

I've tried changing (mixing) ownership of the tables and stored
procedures and using the "WITH EXECUTE AS CALLER" inside "spDeleteA"
but nothing seems to prevent the procedure from doing its unauthorized
job ... except for denying execution rights on "spDeleteA" all-
together.

Any ideas why this is happening?

Thanks,

Dave



.

Relevant Pages

  • Re: SQL CLR Sproc isnt running right
    ... I put a handler on the info messages event ... back saying DBO doesn't have permission to insert records into the table... ... stored procedures work fine inserting data when executed as a user that has ... have execute permissions set for the role that the executing user is in... ...
    (microsoft.public.dotnet.framework.adonet)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > The explanation for this behaviour I have found is that the JDBC ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Records lost in an ADOStoredProc
    ... Use a thread to fire off the stored procedures so that your application ... Let's suppose it updates ... > the CacheSize is set to 1; it is worse when I increase the CacheSize. ... > I don't need to show records, only to execute the store procedure. ...
    (borland.public.delphi.database.ado)