Stored Procedure Ignoring Table Permissions
- From: dschruth@xxxxxxxxx
- Date: Tue, 24 Jul 2007 16:00:17 -0700
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
.
- Follow-Ups:
- Re: Stored Procedure Ignoring Table Permissions
- From: Uri Dimant
- Re: Stored Procedure Ignoring Table Permissions
- From: Erland Sommarskog
- Re: Stored Procedure Ignoring Table Permissions
- Prev by Date: Re: SQL 229 Error
- Next by Date: Preventing windows users accessing a database
- Previous by thread: Re: SQL 229 Error
- Next by thread: Re: Stored Procedure Ignoring Table Permissions
- Index(es):