How to create Login and User in SQL Server passing command line through Visual Studio passing parameters?

Question

public void CriaUsuarioSystem(string userSystem, string pwSystem)
{           
    try
    {
        conexao = new SqlConnection(conexao_BD);
        SqlCommand creatUserSystem = new SqlCommand("CREATE LOGIN @login WITH PASSWORD = @pw " +
                                                        "CREATE USER  @login FOR LOGIN @login", conexao);
        creatUserSystem.Parameters.AddWithValue("@login", userSystem);
        creatUserSystem.Parameters.AddWithValue("@pw",pwSystem);            
        conexao.Open();
        creatUserSystem.ExecuteNonQuery();

    } catch (Exception erro)
    {
        throw erro;
    }
    finally
    {
        conexao.Close();
    }
}

Answer 1

Another way to solve is using Sql Smo, to use it you must download the Sql Feature Pack in https://www.microsoft.com/en-au/download/details.aspx?id=42295 and install the PTB x64 Sharedmanagementobjects.msi

In your project you should reference:
Microsoft.SqlServer.Connectioninfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.Sqlenum

Following example:

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace Sorting
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection connection = new SqlConnection("Server=192.168.X.X;Database=YourDatabase;User Id=YourUser;Password=YourPwd;");
            new Program().CreateLogin(connection, "testeXXX", "testeXXX");
            new Program().CreateUser(connection, "YourDatabase", "testeXXX");

            Console.Write("login e usuário criados com sucesso!");
            Console.Read();
        }

        /// <summary>
        /// Cria o login no servidor sem complexidade de senha.
        /// </summary>
        /// <param name="userSystem">dominio\login ou somente login no caso de sql server</param>
        /// <param name="pwSystem">senha</param>
        public void CreateLogin(SqlConnection connection, string userSystem, string pwSystem)
        {
            Server sqlServerInstance = new Server(new ServerConnection(connection));
            Login login = new Login(sqlServerInstance, userSystem);

            // propriedades do login
            login.DefaultDatabase = "Master";
            login.LoginType = LoginType.SqlLogin;
            login.PasswordPolicyEnforced = false;

            // cria e habilita o login
            login.Create(pwSystem);
            login.Enable();
        }

        /// <summary>
        /// Cria o usuário no banco de dados especificado.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="database"></param>
        /// <param name="userSystem"></param>
        public void CreateUser(SqlConnection connection, string database, string userSystem)
        {
            Server srv = new Server(new ServerConnection(connection));

            // propriedades do user (implementar AddToRole("db_XXX"))
            User user1 = new User(srv.Databases[database], userSystem);
            user1.Login = userSystem;
            user1.Create();
        }

    }

}

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/managing-users-roles-and-logins

Answer 2

As far as I could understand, sql does not accept the login name as a parameter.

To solve this without giving up the use of Parameters, you can use dynamic Sql execution.

For this change your code to:

            conexao = new SqlConnection(conexao_BD);
            SqlCommand creatUserSystem = new SqlCommand(@"
            DECLARE @SCRIPT VARCHAR(1000) = '
            CREATE LOGIN [' + @login + '] WITH PASSWORD = ''' + @pw + ''' 
            CREATE USER [' +  @login + '] FOR LOGIN [' + @login + ']'
            EXECUTE (@SCRIPT)",
            conexao);
            creatUserSystem.Parameters.AddWithValue("@login", userSystem);

            creatUserSystem.Parameters.AddWithValue("@pw", pwSystem);
            conexao.Open();
            creatUserSystem.ExecuteNonQuery();

I hope I’ve helped

Answer 3

There are several options to create a login on the server, the creation of logins is a step that should be done carefully, well thought out, because you must protect your data - both internal and external users.
Other than that, if you run the code below, the login and user will be created on your server/database.

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

var nomeUsuario = "seu_login";

//Realiza uma conexão com o servidor
ServerConnection sc = new ServerConnection(@"<seu_servidor>\<sua_instância>", "<login_sa>", "senha_sa");

//Após a conexão, essa será sua instância do SQL Server
Server svr = new Server(sc);

//Se existir o banco de dados, então iniciará a criação do usuário
var db = svr.Databases["<seu_banco>"];
if (db != null) {               
    //Cria um login no servidor
    Login login = new Login(svr, nomeUsuario);
    login.DefaultDatabase = "<seu_banco>"; //Banco padrão para cada nova conexão
    login.LoginType = LoginType.SqlLogin;
    login.Create("<senha_seu_login>", LoginCreateOptions.None); //Senha para seu novo login
    login.Enable();

    //Cria um usuário local no seu banco de dados a partir do login criado no servidor
    User user = new User(db, nomeUsuario);
    user.UserType = UserType.SqlLogin;
    user.Login = login.Name;
    user.Create();
    user.AddToRole("db_owner"); //Dá permissão geral ao usuário
}

Don’t forget to add the references