Database Schemas and "This statement has attempted to access data whose access is restr

Hello.

In reference to my post yesterday which got removed when i edited this one: After trying a few things i realized that the reason i was getting the error "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." was because a CLR function was executing a stored procedure, which for whatever reason i completely disregarded the fact that functions cannot call stored procedures. After removing that i am faced with the second issue of my assembly being restricted.
I now get the error "This statement has attempted to access data whose access is restricted by the assembly" I am not certain what is causing this, but i think it has something to do with the database security i am using. I created a schema whose owner is dbo. All my database objects are in this schema. The assembly's owner is also dbo. However accessing my database objects in my custom schema will give me the error talking about access restricted by the assembly. Does anyone have any idea why my assembly cannot access data that is owned by the assembly's owner?

Thanks!It looks like you are missing either
DataAccess=DataAccessKind.Read
or SystemDataAccess=SystemDataAccessKind.Read
in your SQLFunction attribute.

Could you please provide your code.

Thanks,
-Vineet.
|||

I am including the attribute but stll get the error, plus did try the SystemDataAccess attribute. This is the code:

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString GetDisplayName(SqlGuid id)

{

if (id.IsNull) return SqlString.Null;

string itemTypeFullName = String.Empty;

using (SqlConnection connection = new SqlConnection("context connection=true"))

{

string sql = @."SELECT [itemtype-full-name] FROM [ItemType] WHERE [id] = '"+id.ToString()+"'";

using (SqlCommand command = new SqlCommand(sql, connection))

{

try

{

connection.Open();

itemTypeFullName = (string)command.ExecuteScalar();

|||I got the same error "This statement has attempted to access data whose access is restricted by the assembly".

I specified DataAccessKind.Read and SystemDataAccessKind.Read and that helped

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]