protected void RoleLogins_Changed(object sender, ItemPickerEventArgs e)
{
SqlServer server = SqlServer.CurrentServer;
server.Connect();
try
{
SqlServerRole role = server.Roles[Request["Role"]];
switch (e.Action)
{
case ItemAction.Add:
role.AddMember(e.Item.Value);
break;
case ItemAction.Remove:
role.DropMember(e.Item.Value);
break;
}
}
catch (Exception ex)
{
ErrorMessage.Text = ex.Message;
return;
}
finally
{
server.Disconnect();
}
}
protected override void OnLoad(EventArgs e)
{
if (!Page.IsPostBack)
{
if (Request["Role"] == null)
{
Response.Redirect("ServerRoles.aspx");
}
SqlServer server = SqlServer.CurrentServer;
server.Connect();
RoleLabel.Text = Request["Role"].ToUpper();
SqlServerRole role = server.Roles[Request["Role"]];
if (role == null)
{
Response.Redirect("ServerRoles.aspx");
}
ArrayList serverLoginNames = new ArrayList();
foreach (SqlLogin login in server.Logins)
{
serverLoginNames.Add(login.Name);
}
RoleLogins.Items = serverLoginNames;
RoleLogins.SelectedItems = role.GetServerRoleMembers();
server.Disconnect();
}
base.OnLoad(e);
}
public static SqlServerRole GetCurrentUserRoles(SqlConnection connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
object[] roles =
new object[]
{
"sysadmin", SqlServerRole.sysadmin,
"dbcreator", SqlServerRole.dbcreator,
"bulkadmin", SqlServerRole.bulkadmin,
"diskadmin", SqlServerRole.diskadmin,
"processadmin", SqlServerRole.processadmin,
"serveradmin", SqlServerRole.serveradmin,
"setupadmin", SqlServerRole.setupadmin,
"securityadmin", SqlServerRole.securityadmin,
};
StringBuilder sqlColumns = new StringBuilder();
for (int i = 0; i < roles.Length; i += 2)
{
sqlColumns
.Append(sqlColumns.Length == 0 ? "" : ", ")
.AppendFormat("IS_SRVROLEMEMBER('{0}')", roles[i]);
}
string sql = "SELECT " + sqlColumns;
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
int column = 0;
SqlServerRole ret = 0;
if (rdr.Read())
{
for (int i = 1; i < roles.Length; i += 2)
{
if (!rdr.IsDBNull(column) && rdr.GetInt32(column) == 1)
{
ret |= (SqlServerRole)roles[i];
}
}
}
return(ret);
}
}
}
