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.
| 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.... statementthen 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 statementthis 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.aspxhope it helps._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
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 |
|
|
|
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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 |
|
|
|
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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
|
|
|
Permissions of CLR Stored Procedures to DB Objects