Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Permissions of CLR Stored Procedures to DB Objects

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-25 : 10:32:05
I need to understand the permissions that a CLR stored procedure needs when it accesses tables. In a dbo TSQL stored procedure it has owner permissions on all dbo tables, so there is no need to grant permissions on tables to the database user.

Some developers recently implemented a CLR stored procedure that returned an error with update permission denied on table name. Once I granted the user account update permission on that table, it was able to execute OK.

I have been looking for a good explanation for the way the permissions to database objects need to be setup for CLR stored procedures. For example, could I have said to modify the procedure to use the EXECUTE AS clause, instead of granting the user account direct permission on the table? Does anyone have any links to good articles on this subject?


Edit:
I have a feeling I'm on my own trying to figure out how this works. I've been searching the web for hours, and I haven't found anything that directly addresses this.


CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-25 : 12:49:11
well clr object is just a function in an assembly.
when you import the assebmly you specify the permissions for code access with the SAFE, EXTERNAL_ACCESS or UNSAFE
keyword. but this is just for .net code access to external resources like disk system or native windows functions.

you import the assembly into the sql server with the CREATE ASSEMBLY FROM file.... statement
then you create SQL objects from that assembly.

next step is to create SQL objects from .net objects.
you do this by using AS EXTERNAL NAME nameFromAssembly in the CREATE PROC statement
this is basicaly a mapping from CLR object to SQL object.
then on that SQL object that you've created you can set permissions like onany other sql object.

this is my playing with CLR in functions but the same goes for sprocs.
http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx

hope it helps.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-25 : 14:38:11
The issue is not setting permissions on the CLR stored procedures; the user has been given proper access to execute the procedure.

The issue is that the dbo CLR stored procedure does not appear to have access to the dbo tables, unless the user running the CLR procedure has been granted specific access to the table.




CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-28 : 11:43:05
what kind of connection string are you using inside the CLR sproc?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-28 : 13:20:23
quote:
Originally posted by spirit1

what kind of connection string are you using inside the CLR sproc?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out



I don’t know.

Unfortunately, I do not have access to the source code for procedures.




CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-28 : 13:55:53
it has to be
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
to be in the same security context.

if it's something else then it assumes the security context of the specified user in that connection.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page
   

- Advertisement -