crausch

crausch

  • NA
  • 53
  • 0

Using Application Roles for SQL server

Apr 11 2008 8:28 AM

I am trying to execute the "sp_setapprole" stored procedure using C# and connecting to a SQL 2000 Server. I want to test the use of application roles. I keep getting an error saying that I am not calling it right. I can find plenty of documentation on the SQL syntax, but I have found literally no examples of the correct way to execute the stored procedure from .NET C# code. Has anyone successfully done this, that would be willing to share their .NET syntax? Even a sample in VB will be ok for me.

Here is my current attempt:


string connString = "my connection stuff goes here";

SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;

// Create the Command object.
SqlCommand MyCmd = new SqlCommand();
MyCmd.CommandText = "sp_setapprole";
MyCmd.CommandType = CommandType.StoredProcedure;
MyCmd.Parameters.Add("@rolename", SqlDbType.NVarChar).Value = "myTestRole";
MyCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = "myPassWd";
MyCmd.Connection = conn;

conn.Open();
int result = MyCmd.ExecuteNonQuery();


There error states that "sp_setapprole was not invoked correctly".