CLR function in 2005

If I execute a function in SQL Server 2005 which does a simple query like
SELECT * FROM TableA in an SQL Command object does it require that the
person who executed the CLR stored procedure has select permission on that
object? Or does the CLR proc execute as a DBO? or is it possible to make it
execute as a DBO if not dispite the user who executed it? just security
questions... want to know how to go about writing new procs in the .NET
CLR.. thanks!Same as for TSQL procedures.
The user executing the proc doesn't have to have permissions to the object t
o access, as long as you
don't have a broken ownership chain.
And, you can control user context when creating the proc with the EXECUTE AS
option of the CREATE
PROC command.
However, as soon as you leave the (SQL Server) sandbox, like accessing a fil
e, you need to do
impersonation in your CLR code if you don't want that code to execute with t
he service account that
the SQL Server service is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:%23qwYGyMMGHA.1532@.TK2MSFTNGP12.phx
.gbl...
> If I execute a function in SQL Server 2005 which does a simple query like
SELECT * FROM TableA in
> an SQL Command object does it require that the person who executed the CL
R stored procedure has
> select permission on that object? Or does the CLR proc execute as a DBO? o
r is it possible to make
> it execute as a DBO if not dispite the user who executed it? just security
questions... want to
> know how to go about writing new procs in the .NET CLR.. thanks!
>|||how would you go about doing impersination to make it look like a CLR
account came from a different account?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Same as for TSQL procedures.
> The user executing the proc doesn't have to have permissions to the object
> to access, as long as you don't have a broken ownership chain.
> And, you can control user context when creating the proc with the EXECUTE
> AS option of the CREATE PROC command.
> However, as soon as you leave the (SQL Server) sandbox, like accessing a
> file, you need to do impersonation in your CLR code if you don't want that
> code to execute with the service account that the SQL Server service is
> using.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%23qwYGyMMGHA.1532@.TK2MSFTNGP12.phx.gbl...
>|||> how would you go about doing impersination to make it look like a CLR account came from a

> different account?
Impersonation is only applicable for UNSAFE or EXTERNAL_ACCESS and when you
access things outside
SQL Server. You cannot even access data inside SQL Server while impersonatin
g. The sole purpose is
to access, say, a file using the end users windows account instead of the se
rvice account. More info
at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1495a7af-2248-4cee-afdb-92
69fb3a7774.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:%2369btZOMGHA.2744@.TK2MSFTNGP10.phx
.gbl...
> how would you go about doing impersination to make it look like a CLR acco
unt came from a
> different account?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl:

> Same as for TSQL procedures.
> The user executing the proc doesn't have to have permissions to the
> object to access, as long as you don't have a broken ownership chain.
>
Actually that is not completely correct. If the statement in the CLR
proc is a SQL statement, like a select or something like that
(CommandType.Text), then the ownership chain breaks. This is like
executing a dynamic SQL statememnt within a T-SQL proc.
However if the call in the CLR proc is a stored proc call to a T-SQL
proc (CommandType.StoredProcedure) then the ownership chain stays
intact, and you only need execute perms on the CLR proc.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Tack Niels :-)
That was totally unexpected to me. I just had to try it with a CLR object an
d indeed TSQL code
executed inside a CLR proc is handled similar to dynamic SQL in a TSQL proc.
Seems to be yet another
reason to access the data through a TSQL proc inside a CLR proc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns976D70AB1234Bnielsbdevelopcom@.20
7.46.248.16...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl:
>
> Actually that is not completely correct. If the statement in the CLR
> proc is a SQL statement, like a select or something like that
> (CommandType.Text), then the ownership chain breaks. This is like
> executing a dynamic SQL statememnt within a T-SQL proc.
> However if the call in the CLR proc is a stored proc call to a T-SQL
> proc (CommandType.StoredProcedure) then the ownership chain stays
> intact, and you only need execute perms on the CLR proc.
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OZTLgp7MGHA.3460@.TK2MSFTNGP15.phx.gbl:

> Tack Niels :-)
Varsagod :-)!!

> That was totally unexpected to me. I just had to try it with a CLR
> object and indeed TSQL code executed inside a CLR proc is handled
> similar to dynamic SQL in a TSQL proc. Seems to be yet another reason
> to access the data through a TSQL proc inside a CLR proc...
>
Definitely!!!
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Client-server simple question

In SQL 2000, if I'm running Query Analyzer on a client machine, and do
something like
Select * Into NewTable from OldTable
the data records don't all come from the server to my client machine and go
back to the server, do they? Doesn't all the data movement actually take
place ON the server?
The only reason I ask is that during this operation, the LAN icon "light"
in the system tray is solidly lit up, while it's not usually that way (it
usually blinks on and off). Maybe that's just status communication to QA,
but the icon is lit completely solid during this operation.
Thanks.
David WalkerThe data should not be zooming back and forth across the LAN.
What is probably being sent is the clock updates to the QA.
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:OYnpflb#EHA.3368
@.TK2MSFTNGP15.phx.gbl:

> The data should not be zooming back and forth across the LAN.
> What is probably being sent is the clock updates to the QA.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
OK, thanks. That's what I hoped. The "light" being on solid is strange
for clock updates to the QA, but ... thanks.
David Walker

Client-server simple question

In SQL 2000, if I'm running Query Analyzer on a client machine, and do
something like
Select * Into NewTable from OldTable
the data records don't all come from the server to my client machine and go
back to the server, do they? Doesn't all the data movement actually take
place ON the server?
The only reason I ask is that during this operation, the LAN icon "light"
in the system tray is solidly lit up, while it's not usually that way (it
usually blinks on and off). Maybe that's just status communication to QA,
but the icon is lit completely solid during this operation.
Thanks.
David Walker
The data should not be zooming back and forth across the LAN.
What is probably being sent is the clock updates to the QA.
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <quickening@.msn.com> wrote in news:OYnpflb#EHA.3368
@.TK2MSFTNGP15.phx.gbl:

> The data should not be zooming back and forth across the LAN.
> What is probably being sent is the clock updates to the QA.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
OK, thanks. That's what I hoped. The "light" being on solid is strange
for clock updates to the QA, but ... thanks.
David Walker