/// <summary>
/// Procedure processes normal procedures only excluding encrypted, system and CLR procedures
/// </summary>
/// <param name="db">database</param>
/// <param name="rg">Regular expression for replacing text</param>
/// <param name="newName">New name of renamed table (old one is stored in regular expression</param>
/// <param name="rzlt">List with names modified objects.</param>
private static void CorrectStoredProcedures(Database db, Regex rg, string newName, List <AlteredDependencyDbo> rzlt)
{
DataTable dt = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
var procList =
from sp in dt.AsEnumerable()
where String.Compare(sp.Field <string>("Schema"), "sys", true) != 0
select sp.Field <string>("Name");
foreach (string name in procList)
{
StoredProcedure sp = db.StoredProcedures[name];
if (sp == null)
{
continue;
}
if (!(sp.ImplementationType == ImplementationType.SqlClr || sp.IsEncrypted || sp.IsSystemObject))
{
string body = sp.TextBody;
if (String.IsNullOrEmpty(body))
{
continue;
}
if (rg.IsMatch(body))
{
sp.TextBody = rg.Replace(body, newName);
sp.Alter();
rzlt.Add(new AlteredDependencyDbo()
{
ObjectType = DbObjectType.StoredProcedure, Name = sp.Name
});
}
}
}
}
private void cmdEncryptObjects_Click(object sender, EventArgs e)
{
try
{
DialogResult dlgRes = MessageBox.Show("Are you sure you want to encrypt -> " + cboComboBox.Text + "\n\n\nPlease confirm if a backup has been taken before this operation!", "Please Confirm", MessageBoxButtons.YesNo);
if (dlgRes == DialogResult.Yes)
{
dlgRes = MessageBox.Show("Please note that the encryption is not reversible. Please confirm that you want to encrypt -> " + cboComboBox.Text + "\n\n\nPlease confirm if a backup has been taken before this operation!", "Please Confirm", MessageBoxButtons.YesNo);
}
if (dlgRes == DialogResult.No)
{
return;
}
SqlConnectionInfo sci = new SqlConnectionInfo(txtServerName.Text);
if (!chkWindowsAuth.Checked)
{
sci.UseIntegratedSecurity = false;
sci.UserName = txtUserID.Text;
sci.Password = txtPassword.Text;
}
else
{
sci.UseIntegratedSecurity = true;
}
var serverConnection = new ServerConnection(sci);
var srv = new Server(serverConnection);
try // Check to see if server connection details are ok.
{
var db = new Database();
db = srv.Databases[cboComboBox.Text.TrimStart('[').TrimEnd(']')];
if (db == null)
{
throw new Exception("Error! Unable to select");
}
Console.WriteLine("Encrypted stored procedures: ");
var sp = new StoredProcedure();
for (int i = 0; i < db.StoredProcedures.Count; i++)
{
sp = db.StoredProcedures[i];
if (!sp.IsSystemObject) // Exclude System stored procedures
{
if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
{
sp.TextMode = false;
sp.IsEncrypted = true;
sp.TextMode = true;
try
{
sp.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", sp.Name, excep.Message);
}
Console.WriteLine(" " + sp.Name); // display name of the SP.
_logger.Info("Encrypting Proc =>{0}", sp.Name);
}
}
}
var udfs = new UserDefinedFunction();
for (int i = 0; i < db.UserDefinedFunctions.Count; i++)
{
udfs = db.UserDefinedFunctions[i];
if (!udfs.IsSystemObject)
{
if (!udfs.IsEncrypted)
{
udfs.TextMode = false;
udfs.IsEncrypted = true;
udfs.TextMode = true;
try
{
udfs.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", udfs.Name, excep.Message);
}
Console.WriteLine(" " + udfs.Name);
_logger.Info("Encrypting UDF =>{0}", udfs.Name);
}
}
}
Microsoft.SqlServer.Management.Smo.View viewobj = null;
for (int i = 0; i < db.Views.Count; i++)
{
viewobj = db.Views[i];
if (!viewobj.IsSystemObject)
{
if (!viewobj.IsEncrypted)
{
viewobj.TextMode = false;
viewobj.IsEncrypted = true;
viewobj.TextMode = true;
try
{
viewobj.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", viewobj.Name, excep.Message);
}
Console.WriteLine(" " + viewobj.Name); // display name of the SP.
_logger.Info("Encrypting View =>{0}", viewobj.Name);
}
}
}
MessageBox.Show("Process has completed succssfully. Please refer to the application logs for more information", "Operation Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception InnerEx)
{
_logger.Info("Server details are incorrect;"
+ " please retry with proper details");
MessageBox.Show("Error\n" + "Server details are incorrect;"
+ "\nPlease retry with proper details.\n\n" + InnerEx.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
_logger.Fatal(ex, "Unable to connect to the DB");
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GrantAccessToSchemaObjects()
{
var helper = new TestHelper();
try
{
var schema = helper.GetSchema();
//schema.Owner = helper.GetUser().Name;
//schema.Alter();
var table = new Table(helper.GetDatabase(), "Table1", schema.Name);
table.Columns.Add(new Column(table, "Col1", DataType.Int));
table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax));
table.Create();
helper.AddCleanup(table);
var view = new View(helper.GetDatabase(), "View1", schema.Name)
{
TextMode = false,
TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name)
};
//view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name);
view.Create();
helper.AddCleanup(view);
var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name)
{
TextMode = false,
DataType = DataType.DateTime,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Scalar,
ImplementationType = ImplementationType.TransactSql,
TextBody = "BEGIN RETURN GETDATE() END"
};
scalarTsqlFn.Create();
helper.AddCleanup(scalarTsqlFn);
var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name)
{
TextMode = false,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Inline,
ImplementationType = ImplementationType.TransactSql,
TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name)
};
inlineTsqlFn.Create();
helper.AddCleanup(inlineTsqlFn);
// TODO: Create table valued function
// TODO: Create Clr scalar func
// TODO: Create Clr inline func (Exists?)
// TODO: Create Clr table valued func
// TODO: Create Clr Aggregate
var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name)
{
TextMode = false,
AnsiNullsStatus = false,
QuotedIdentifierStatus = false,
TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name)
};
proc.Create();
helper.AddCleanup(proc);
// TODO: Create Clr Sproc
// TODO: Create Constraint
// TODO: Create Queue
// TODO: Create Statistic
// TODO: Create Synonym
var user = helper.GetUser();
var permissable = new IObjectPermission[]
{
table,
view,
scalarTsqlFn,
inlineTsqlFn,
proc,
};
permissable.Do(tg => tg.GrantAll(user.Name));
permissable.Do(tg => tg.DenyAll(user.Name));
permissable.Do(tg => tg.RevokeAll(user.Name));
// change all owners
table.Owner = user.Name;
table.Alter();
view.Owner = user.Name;
view.Alter();
scalarTsqlFn.Owner = user.Name;
scalarTsqlFn.Alter();
inlineTsqlFn.Owner = user.Name;
inlineTsqlFn.Alter();
proc.Owner = user.Name;
proc.Alter();
}
finally
{
helper.Cleanup();
}
}
