protected override void Execute(CodeActivityContext context)
{
var console = context.GetExtension <ActivityConsole>();
if (console == null)
{
console = new ActivityConsole();
}
string sqlStatement =
"USE [{0}]\n" +
"DECLARE @Username nvarchar(max);\n" +
"SET @Username = '******' + '{2}';\n" +
"SET NOCOUNT ON;\n" +
"DECLARE @SQL NVARCHAR(4000);\n" +
"SET @SQL = 'CREATE USER [' + @Username + '] FOR LOGIN [' + @Username + '] WITH DEFAULT_SCHEMA=[{3}]';\n" +
"EXECUTE(@SQL);";
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString.Get(context));
string databaseName = connectionStringBuilder.InitialCatalog;
string loginName = LoginName.Get(context);
string message = string.Format("Successfully created (or exists) SQL Server database user account '{0}'.", loginName);
string defaultSchema = DefaultSchema.Get(context);
if (string.IsNullOrEmpty(defaultSchema))
{
defaultSchema = "dbo";
}
if (IsWindowsLogin.Get(context))
{
if (IsLocalSQLMachineAccount.Get(context))
{
sqlStatement = string.Format(sqlStatement, databaseName, "HOST_NAME()", "\\" + loginName, defaultSchema);
}
else
{
if (string.IsNullOrEmpty(DomainName.Get(context)))
{
throw new ArgumentException("Domain name must be specified for windows domain logins.");
}
sqlStatement = string.Format(sqlStatement, databaseName, "'" + DomainName.Get(context) + "'", "\\" + loginName, defaultSchema);
}
}
else
{
sqlStatement = string.Format(sqlStatement, databaseName, "", loginName, defaultSchema);
}
using (SqlConnection connection = new SqlConnection(ConnectionString.Get(context)))
{
using (SqlCommand command = new SqlCommand(sqlStatement, connection))
{
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (ex.Number != 15023) //Already exists, ignore this error.
{
throw;
}
}
console.WriteLine(message);
}
}
}
protected override void Execute(CodeActivityContext context)
{
var console = context.GetExtension <ActivityConsole>();
if (console == null)
{
console = new ActivityConsole();
}
string sqlStatement =
"DECLARE @Username nvarchar(max);\n" +
"SET @Username = '******' + '{1}';\n" +
"SET NOCOUNT ON;\n" +
"DECLARE @SQL NVARCHAR(4000);\n" +
"SET @SQL = 'CREATE LOGIN [' + @Username + '] {2} WITH {3} DEFAULT_DATABASE=[{4}]';\n" +
"EXECUTE(@SQL);";
string loginName = LoginName.Get(context);
string message = string.Format("Successfully created (or exists) SQL Server user login '{0}'.", loginName);
string defaultDatabase = DefaultDatabase.Get(context);
if (string.IsNullOrEmpty(defaultDatabase))
{
defaultDatabase = "master";
}
if (IsWindowsLogin.Get(context))
{
if (IsLocalSQLMachineAccount.Get(context))
{
sqlStatement = string.Format(sqlStatement, "HOST_NAME()", "\\" + loginName, "FROM WINDOWS", "", defaultDatabase);
}
else
{
if (string.IsNullOrEmpty(DomainName.Get(context)))
{
throw new ArgumentException("Domain name must be specified for windows domain logins.");
}
sqlStatement = string.Format(sqlStatement, "'" + DomainName.Get(context) + "'", "\\" + loginName, "FROM WINDOWS", "", defaultDatabase);
}
}
else
{
string password = Password.Get(context);
sqlStatement = string.Format(sqlStatement, "", loginName, "", "PASSWORD=''" + password + "'',", defaultDatabase);
}
using (SqlConnection connection = new SqlConnection(ConnectionString.Get(context)))
{
using (SqlCommand command = new SqlCommand(sqlStatement, connection))
{
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (ex.Number != 15025) //Already exists, ignore error
{
throw;
}
}
console.WriteLine(message);
}
}
}
