"Brian Henry" <nospam@nospam.com> wrote in message
news:eia7VRZMGHA.2336@TK2MSFTNGP12.phx.gbl...
> Ok I'm confused on this... in T-SQL Say I have TableA and it has no select
> permissions on it for the user... but the user has a stored procedure with
> execute permission on it and contents of it is SELECT * from TableA...
> well of course this executes! and returns the TableA contents... where
> doing a SELECT * Fromt TableA in a stand alone query returns a permission
> error... now when I do a similar thing in the new CLR compiled stored
> procedures using an ADO.NET Command object... with SELECT * FROM TableA it
> comes back with the permission error that I cant select from tablea...
> why?! if i can do it in T-SQL why can't I in a CLR stored procedure? which
> is more secure to start with then a plain text T-SQL statement! I'm trying
> to convert some T-SQL procs to ADO.NET's CLR store procs on SQL Server
> 2005 but this hitch kinda put everything im doing at a hault because i
> cant do anything if i cant get select permission! and security wise i dont
> want to give people select permission on tables... anything to help with
> this problem? thanks!
>

"Alazel Acheson [MS]" <alaz***@online.microsoft.com> wrote in message
news:%236lVdTbMGHA.2064@TK2MSFTNGP09.phx.gbl...
> Short answer:
> use EXECUTE AS OWNER clause in your CREATE PROC statement.
>
> Longer answer:
> This works by default in TSQL due to ownership chaining -- if the table
> and procedure are owned by the same entity, then user permissions are not
> checked against the table, just against the procedure.  In Sql Server
> 2005, there is a new paradigm using the EXECUTE AS clause on various DDL
> statements.  This allows you to specify that the code should execute with
> the credentials of a particular entity, including a couple of dynamic ids
> (USER, OWNER, SELF).  For better security, .Net procedures execute as USER
> by default.  Executing as OWNER is the closest match to the TSQL default
> ownership chaining.
>
> --
> ~Alazel
>
> Alazel Acheson
> Software Developer
> Microsoft SQL Server
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Brian Henry" <nospam@nospam.com> wrote in message
> news:eia7VRZMGHA.2336@TK2MSFTNGP12.phx.gbl...
>> Ok I'm confused on this... in T-SQL Say I have TableA and it has no
>> select permissions on it for the user... but the user has a stored
>> procedure with execute permission on it and contents of it is SELECT *
>> from TableA... well of course this executes! and returns the TableA
>> contents... where doing a SELECT * Fromt TableA in a stand alone query
>> returns a permission error... now when I do a similar thing in the new
>> CLR compiled stored procedures using an ADO.NET Command object... with
>> SELECT * FROM TableA it comes back with the permission error that I cant
>> select from tablea... why?! if i can do it in T-SQL why can't I in a CLR
>> stored procedure? which is more secure to start with then a plain text
>> T-SQL statement! I'm trying to convert some T-SQL procs to ADO.NET's CLR
>> store procs on SQL Server 2005 but this hitch kinda put everything im
>> doing at a hault because i cant do anything if i cant get select
>> permission! and security wise i dont want to give people select
>> permission on tables... anything to help with this problem? thanks!
>>
>
>