Prev: sqlcrl exception
Next: Left Join - which table is left
From: moondaddy on 29 Mar 2010 15:43
I'm running a report on SSRS that's based on a sproc - and this sproc uses a
sql udf. The report runs fine when run it from the sql server machine, but
it fails when I run it from other machines and I'm suspecting that this is
because I have not assigned any permissions to the udf. I've assigned the
role 'ReportUsers' to all sprocs which allows the reports to be called from
other machines, but when I look at the udf properties, there are no roles
assigned to it and I don't see anything in SSMS that allows the assigning of
security permissions to udfs.

Is it possible to assign roles to udfs? and if so, how?

I assign permissions to sprocs via this script:

declare stored_procedures_cursor cursor local fast_forward
for
select
quotename(routine_schema) + '.' + quotename(routine_name)
from
information_schema.routines
where
routine_type = 'PROCEDURE'
and objectproperty(object_id(routine_schema + '.' + routine_name),
'IsMSShipped') = 0

open stored_procedures_cursor

while 1 = 1
begin
fetch next from stored_procedures_cursor into @sp

if @@error <> 0 or @@fetch_status <> 0 break

set @sql = N'GRANT EXECUTE ON ' + @sp + N' TO ' + @user
execute sp_executesql @sql
end

close stored_procedures_cursor
deallocate stored_procedures_cursor


thanks.



--
moondaddy(a)newsgroup.nospam

 | 
Pages: 1
Prev: sqlcrl exception
Next: Left Join - which table is left