Re: Permission denied in table yet allowed access in Sproc but still no access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 08:07:46 -0500

Dynamic SQL breaks the ownership chain so permissions on the referenced
tables are needed in that case. Also, if the objects are in different
databases, both databases need to have the same owner and cross-database
chaining needs to be enabled for those databases. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"ryu" <blizzardstorm8899@yahoo.com> wrote in message
news:%235V2F4oiEHA.632@TK2MSFTNGP12.phx.gbl...
> I have checked. The owner for both obj is dbo
>
> "Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message
> news:uxJwHsoiEHA.3876@TK2MSFTNGP12.phx.gbl...
> > Is the base table owned by the same user as the sproc? SQL Server checks
> > permissions at each break in the ownership chain, so if the owners are
> > different then the permissions on the base table would be applied.
> >
> > Cheers,
> > Graeme
> > -- 
> > ----
> > Graeme Malcolm
> > Principal Technologist
> > Content Master Ltd.
> > www.contentmaster.com
> >
> >
> > "Ryu" <blizzardstorm8899@yahoo.com> wrote in message
> > news:%23yl6zfoiEHA.1356@TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > I understand that when I am denied access from the table but given the
> > right
> > permission in a sproc accessing the table, I am still able to access the
> > table. However
> > I have a table which I am denied access but given the correct permission
> > yet
> > I am still not able to access. I have checked the roles and they seem
> > fine.
> > What else can I check?
> >
> >
> >
> >
>
>

Relevant Pages

  • Re: object level permissions being lost when migrating
    ... I am talking about SQL Logins, ... can go to the object and see that user X has permissions XYZ on that object, ... I then restored the databases and re-synched the users via the ...
    (microsoft.public.sqlserver.security)
  • Re: one SQL DB getting info from another - user setup?
    ... It appears from your narrative that the Main and DB1 databases ... EXEC sp_helpdb 'Main' ... SQL 2000 SP3 and is turned off by default. ... > In the permissions for Main, USER1 and USER2 are listed, but have NO ...
    (microsoft.public.sqlserver.security)
  • Re: very odd user permissions problem from a restore
    ... unless I give them dbo permissions. ... Mgmt Studio does not check which databases you have access to, ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling Access to data from multiple databases
    ... this doesn't work since within the body of the stored procedure it reaches ... procedure fails on permissions. ... read only permission to all databases my stored procedure depends on. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: SQL CE Synching Problems
    ... Have you granted IUSER_ServerName access to your publication within SQL ... It looks like the permissions problem is getting access to the publication. ... so the issue has to be between the server tools and the publisher. ... > A request to send data to the computer running IIS has failed. ...
    (microsoft.public.sqlserver.ce)