C#动态创建数据库并只允许特定用户登录该数据库

最新推荐文章于 2020-12-15 11:23:52 发布

原创最新推荐文章于 2020-12-15 11:23:52 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

Dot Net 专栏收录该内容

21 篇文章

订阅专栏

本文介绍如何使用C#代码在SQL Server中创建数据库、添加用户，并授予用户特定表的操作权限，最后通过该用户进行数据查询。

 protected void Page_Load(object sender, EventArgs e)
    {
        //參考
        //http://tech.cncms.com/shujuku/mssql/96236.html
        //http://bbs.youkuaiyun.com/topics/360120482
    }

    //創建數據庫
    protected void btnCreateDB_Click(object sender, EventArgs e)
    {
        StringBuilder sb = new StringBuilder();
        //連接到master數據庫
        string connString = "Server=QADB;Integrated security=SSPI;database=master";
        sb.AppendLine(" CREATE DATABASE MyDatabase ON PRIMARY ");
        sb.AppendLine(" (NAME=MyDatabase_Data,");
        sb.AppendLine(" FILENAME='F:\\MyDatabaseData.mdf',");
        sb.AppendLine(" SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=10%) ");
        sb.AppendLine(" LOG ON (NAME=MyDatabase_Log,");
        sb.AppendLine(" FILENAME='F:\\MyDatabaseLog.ldf',");
        sb.AppendLine(" SIZE=3MB,MAXSIZE=5MB,FILEGROWTH=10%) ");

        using (SqlConnection conn = new SqlConnection(connString))
        {
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            try{
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch(System.Exception ex){
                Response.Write("Failed:"+ex.ToString());
            }
        }
    }

    //新增用戶
    protected void btnAddUser_Click(object sender, EventArgs e)
    {
        //連接到master數據庫
        string connString = "Server=QADB;Integrated security=SSPI;database=master";

        string sql = String.Empty;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                //添加登錄名和密碼
                sql = "exec sp_addlogin 'root','abc@123','MyDataBase'";
                cmd.ExecuteNonQuery();
                //添加權限(默認只有連接的權限)
                sql = "USE MyDataBase exec sp_grantdbaccess 'root'";
                cmd.ExecuteNonQuery();
                //指定登錄名[root]對 Test表有 增刪改查的權限(Test表不是動態創建的,是在db裏面創建的)
                sql = "USE MyDataBase GRANT SELECT , INSERT , UPDATE , DELETE ON Test TO [root]";
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                Response.Write("Failed:" + ex.ToString());
            }
        }


    }
    //使用創建的用戶對表進行查詢
    protected void btnTest_Click(object sender, EventArgs e)
    {
        //連接到動態創建的MyDatabase數據庫,并使用root/abc@123帳號登陸
        string strConn = "Persist Security Info=False;User ID=root;PWD=abc@123;Initial Catalog=MyDatabase;Data Source=QADB";

        using (SqlConnection conn = new SqlConnection(strConn))
        {
            //表Test是在MyDatabase數據庫裏面手動創建的
            SqlCommand cmd = new SqlCommand("select COUNT(1) from Test", conn);
            try
            {
                conn.Open();
                int i = (int)cmd.ExecuteScalar();
                Response.Write("Succesfull:" + i);
            }
            catch (System.Exception ex)
            {
                Response.Write("Failed:" + ex.ToString());
            }
        }

    }