Altering (or recreating) a Stored Procedure "header"

We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.Could you please explain how you are recreating the SP? If you are doing it from the UI or something then you may want to post this question in the Tools forum. Otherwise, please post the DDL statement(s) and the reprot steps.|||I believe I see what we were (or in this case weren't) doing... The USE statement is necessary to point the scripts to the correct database. We were seeing the outcome of confusing the master database with our application database. Thanks much for anyone stopping to consider our "dilemma".|||In essence, we are checking for existence of the stored procedure in the system table first, I believe sys.objects. If we find it there first, we drop it. Then we follow up by recreating it. But, as I mentioned in a follow up to our original post, the issue turned out to be a case of not using the USE statement. So what I thought was showing up in our application database was actually showing up in the master database. Not quite what we were shooting for. So hence the confusion.

Allow broadcast from CLR Stored Procedure without using permission set 'Unsafe'

Hello,

I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net.Sockets;

public partial class UserDefinedFunctions

{

[SqlProcedure]

public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)

{

System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();

byte[] datagram = message.GetUnicodeBytes();

client.Send(datagram, datagram.Length, (string)address, (int)port);

}

};

I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:

Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)

at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)

at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)

at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)

I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.

Anyone who has something like this ?

No, you can not alter any of the existing permission sets. What you can do is, by using CAS, further restricting what a permission set can do by setting (for that assembly) specific CAS policies. However, you still have to create the assembly with whatever permission set, that is required.

Niels
|||You can't modify the built-in SQL CLR permission sets, but you can restrict your assembly's permission grant by using assembly-level permission attributes. However, your assembly would still need to be deployed at the UNSAFE level in order to be granted the SecurityPermission\UnmanagedCode permission that it needs.