Hello Harry,

It sounds like you'd like get table from SQLDatabase with the permissions
associated with an application role , correct? I'm not sure what you mean
by "Named DataSet". (I assume it is "Strong Typed DataSet" which is
generated by VS 2005.) If I misunderstood anything here, please correct me.

If this is the case, my suggestion is to check Connection.StateChange
event.We can call setapprole function after connection openning.
For example: If the connection state is changed from "Close" to "Open", I
actives application role.

#1--------------------------------------------------------------------------
--------------
DataSet1TableAdapters.Table_1TableAdapter tta = new
ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
tta.Connection.StateChange += new
System.Data.StateChangeEventHandler(Connection_StateChange);
DataSet1 ds = new DataSet1();
tta.Fill(ds.Table_1);

void Connection_StateChange(object sender, System.Data.StateChangeEventArgs
e)
{
           if (e.OriginalState == System.Data.ConnectionState.Closed &&
e.CurrentState == System.Data.ConnectionState.Open)
           {
               System.Data.SqlClient.SqlCommand sc = new
System.Data.SqlClient.SqlCommand();
               sc.Connection = (System.Data.SqlClient.SqlConnection)sender;
               sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
'987G^bv876sPY)Y5m23'";
               sc.ExecuteNonQuery();
           }
}
----------------------------------------------------------------------------
------------

In addition, we can add this method in partical class. Thereby, we needn't
add this event each time.
For example:

#2--------------------------------------------------------------------------
--------------
DataSet1TableAdapters.Table_1TableAdapter tta = new
ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
tta.setapprole();
DataSet1 ds = new DataSet1();
tta.Fill(ds.Table_1);

namespace ConsoleApplication61.DataSet1TableAdapters
{
   partial class Table_1TableAdapter
   {
       public void setapprole()
       {
           Connection.StateChange += new
System.Data.StateChangeEventHandler(Connection_StateChange);
       }

       void Connection_StateChange(object sender,
System.Data.StateChangeEventArgs e)
       {
           if (e.OriginalState == System.Data.ConnectionState.Closed &&
e.CurrentState == System.Data.ConnectionState.Open)
           {
               System.Data.SqlClient.SqlCommand sc = new
System.Data.SqlClient.SqlCommand();
               sc.Connection = (System.Data.SqlClient.SqlConnection)sender;
               sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
'987G^bv876sPY)Y5m23'";
               sc.ExecuteNonQuery();
           }
       }
   }
}
----------------------------------------------------------------------------
------------

Hope this helps. Please feel free to update here again, if you have any
more concern.We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.