private void LoadUserRoles()
{
SqlServer.Management.Smo.Server server = this.context.Server;
this.userRole = UserRoles.None;
if ((server.ConnectionContext.UserProfile & ServerUserProfiles.SALogin) > 0)
{
this.userRole |= UserRoles.SysAdmin;
}
if (this.Version.Major >= 9)
{
Database msdb = server.Databases["msdb"];
if (msdb != null)
{
if (msdb.IsMember("SQLAgentOperatorRole"))
{
this.userRole |= UserRoles.AgentOperator;
}
if (msdb.IsMember("SQLAgentReaderRole"))
{
this.userRole |= UserRoles.AgentReader;
}
}
}
this.userRole |= UserRoles.AgentUser;
}
private void LoadLogins()
{
// figure out what rights the user has.
SqlServer.Management.Smo.Server server = this.context.Server;
// see if the user is a sysadmin. At the moment sysadmins can assign
// job ownership to any user. Non sysadmins cannot. Operators can see jobs owned by anyone
if ((this.UserRole & UserRoles.SysAdmin) > 0 || (this.UserRole & UserRoles.AgentOperator) > 0)
{
System.Collections.Specialized.StringCollection validLoginNames = new System.Collections.Specialized.StringCollection();
foreach (SMO.Login login in server.Logins)
{
if (SMO.LoginType.WindowsGroup != login.LoginType)
{
//For Msx jobs, only add logins that are members of the sysadmin role.
if (!this.targetLocalServer)
{
if (login.IsMember("sysadmin"))
{
validLoginNames.Add(login.Name);
}
}
else
{
//Otherwise, if this is NOT an Msx jobs, just add it.
validLoginNames.Add(login.Name);
}
}
}
//validLoginNames will not include the current connection's trusted user therefore
//add it to the owners string array. This will allow the value to be seen (and selected) in
//the job properties drop down.
//Only add the name if it doesn't already exist in the collection
if (!validLoginNames.Contains(TrueLogin))
{
validLoginNames.Add(TrueLogin);
}
this.owners = new string[validLoginNames.Count];
validLoginNames.CopyTo(this.owners, 0);
}
else
{
// the user is the only person allowed to own the job
this.owners = new string[1] {
server.ConnectionContext.TrueLogin
};
}
}
/// <summary>
/// check if given schedule data is shared schedule or not
/// </summary>
/// <param name="js"></param>
/// <param name="jobScheduleData"></param>
/// <returns></returns>
private bool IsSharedSchedule(JobServer js, JobScheduleData jobScheduleData)
{
if (js == null)
{
throw new ArgumentNullException("js");
}
SqlServer.Management.Smo.Server srv = js.Parent as SqlServer.Management.Smo.Server;
if ((srv == null) || (srv.Information.Version.Major < 9))
{
// Shared Schedules not supported prior Yukon
return(false);
}
else
{
// with Yukon all schedules are now shared
return(true);
}
}
/// <summary>
/// Creates/ drops database as requested.
/// </summary>
/// <param name="args">
/// [0] = CreateDatabase, DropDatabase
/// [1] = Name of Database
/// </param>
public static void Run(string[] args)
{
if (!args.Any() || args.Length < 2)
{
throw new InvalidArgumentException("Incomplete arguments provided.");
}
try
{
var dbName = args[1];
s_configJson = Config.Load(ConfigPath);
LoadActiveConnectionStrings();
foreach (KeyValuePair <string, string> activeConnString in s_activeConnectionStrings)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder((activeConnString.Value));
if (!Utils.IsAzureSqlServer(builder.DataSource))
{
builder.InitialCatalog = DB_Master;
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
SqlServer.Management.Smo.Server server = new SqlServer.Management.Smo.Server(new ServerConnection(conn));
ServerConnection context = server.ConnectionContext;
if (args[0] == "CreateDatabase")
{
// We do not create database for HGS-VBS since SQL Server for AASVBS and HGSVBS connection strings is same.
// Do not create database for NP connection string, since server is always same as TCP
if (activeConnString.Key != TCPConnectionStringHGSVBS && activeConnString.Key != NPConnectionString)
{
//Create a new database
CreateDatabase(dbName, context);
Console.WriteLine($"Database [{dbName}] created successfully in {builder.DataSource}");
}
// Update Config.json accordingly
builder.InitialCatalog = dbName;
UpdateConfig(activeConnString.Key, builder);
}
else if (args[0] == "DropDatabase")
{
// We do not drop database for HGS-VBS since SQL Server for AASVBS and HGSVBS connection strings is same.
// Do not drop database for NP connection string, since server is always same as TCP
if (activeConnString.Key != TCPConnectionStringHGSVBS && activeConnString.Key != NPConnectionString)
{
// Drop Northwind for test run.
DropIfExistsDatabase(dbName, context);
Console.WriteLine($"Database [{dbName}] dropped successfully in {builder.DataSource}");
}
}
else
{
Console.WriteLine($"Utility '{args[0]}' not supported in {builder.DataSource}");
}
}
}
else
{
Console.WriteLine($"Database Utilities are not supported for Azure SQL in {activeConnString.Key}");
}
}
if (args[0] == "CreateDatabase")
{
// Update config.json with Initial Catalog = <dbName> for "Active Connection Strings"
Config.UpdateConfig(s_configJson, ConfigPath);
}
}
catch (Exception e)
{
throw new Exception($"{args[0]} execution failed with Error: {e.Message}");
}
}
