数据库应用程序角色配置

1、添加角色
exec sp_addapprole 'denlu','123456'
帐号：denlu 密码：123456
2、查看角色是否存在
exec sp_setapprole 'denlu','123456'

3、更改角色密码和架构

alter application role [myapprole]

with name=[denlu] ,

password='234567' ,

DEFAULT_SCH EMA = [dbo]

3、配置解色权限
在角色可操作的表中鼠标选择表点右建属性=》权限
在打开的窗体搜索角色，点搜索出来用户和角色的搜索窗，对象类型选择应用角色，点击下方的浏览选择角色如“denlu”
选择确定以后在属性窗里用户和角色栏里就多了一个"denlu"角色，选择角色在它对应的下方权限设置权限，配置就完成了
4、使用
例：C#
    public class getconnection
    {
        private System.Data.SqlClient.SqlConnection mypunconnection;
        public getconnection(string csstring, string awe, string olk)
        {
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(csstring);
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sp_setapprole", connection);
            try
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Clear();
                command.Parameters.Add("@rolename", System.Data.SqlDbType.NVarChar).Value = awe;
                command.Parameters.Add("@password", System.Data.SqlDbType.NVarChar).Value = olk;
                connection.Open();
                command.ExecuteNonQuery();
                this.mypunconnection = connection;

            }
            catch (Exception exception1)
            {
                Exception exception = exception1;
                command.Dispose();
                connection.Close();
                System.Data.SqlClient.SqlConnection.ClearPool(connection);
                connection.Dispose();
            }
        }
        public System.Data.SqlClient.SqlConnection connection
        {
            get
            {
                return this.mypunconnection;
            }
        }
        public void Dispose()
        {
            if (this.mypunconnection != null)
            {
                this.mypunconnection.Close();
                System.Data.SqlClient.SqlConnection.ClearPool(this.mypunconnection);
                this.mypunconnection.Dispose();
            }
        }
    }

    public class usertity
    {
        public string getuser()
        {
            getconnection myconn = null;
            myconn = new getconnection(System.Configuration.ConfigurationManager.ConnectionStrings["maindatabase"].ConnectionString, "denlu", "123456");
            System.Data.SqlClient.SqlTransaction transa = myconn.connection.BeginTransaction();
            System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand();
            try
            {
                //检查字符串合理性
                myCommand.CommandText = "select top 1 id from user where userid =11";
                Object myo = myCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
            }
            finally
            {
                myconn.Dispose();
            }
            return "";

        }

    }