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); } } }