none
C#数据库编程中SqlCommand不能执行"DROP LOGIN"或“EXEC sp_droplogin”来删除SQL Server登陆名 RRS feed

  • 问题

  • C#数据库编程中SqlCommand不能执行"DROP LOGIN"或“EXEC sp_droplogin”来删除SQL Server登陆名,请问怎么实现,或有什么其它方法可以在C#程序里删除SQL Server登录名。
    2018年7月5日 5:34

全部回复

  • Hi,

    你可以将sp_droplogin (Transact-SQL)命令放入一个.sql的文件中,然后使用c#执行此文件:

            public static void Main()
            {
                string scriptDirectory = "c:\\temp\\sqltest\\";
                string sqlConnectionString = "Integrated Security=SSPI;" +
                "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
                DirectoryInfo di = new DirectoryInfo(scriptDirectory);
                FileInfo[] rgFiles = di.GetFiles("*.sql");
                foreach (FileInfo fi in rgFiles)
                {
                    FileInfo fileInfo = new FileInfo(fi.FullName);
                    string script = fileInfo.OpenText().ReadToEnd();
                    using (SqlConnection connection = new SqlConnection(sqlConnectionString))
                    {
                        Server server = new Server(new ServerConnection(connection));
                        server.ConnectionContext.ExecuteNonQuery(script);
                    }
                }
            }

    参考:Execute a large SQL script (with GO commands)

    下面这篇文章讲了如何移除Sql Server用户名:

    Few Step(s) to Remove SQL Server Database User(s)

    希望对你有帮助。

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年7月5日 9:28
  • DROP Login是TSQL的内容,不是标准的SQL语句,需要用Stanly Fan提到的方法,以脚本的形式运行。



    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2018年7月6日 0:01
  • https://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
    using System.IO;
    using System.Data.SqlClient;
    
    public partial class ExcuteScript : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";
    
        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");
    
        SqlConnection conn = new SqlConnection(sqlConnectionString);
    
        Server server = new Server(new ServerConnection(conn));
    
        server.ConnectionContext.ExecuteNonQuery(script);
        }
    }


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2018年7月6日 0:01