我已经创建了以下代码,用于从C#代码以编程方式将登录和用户添加到SQL Server2016。我还需要为新用户分配对数据库进行读写访问的相关角色,但在使用下面的cmdText字符串时,我找不到任何对我有帮助的示例。
我假设我需要添加到这个新用户的角色是:
db_datareader
db_datawriter
这个新创建的用户只需要访问一个数据库,并且能够从现有数据库表中添加/编辑/删除/更新记录。
我只需要添加这个新的登录名/用户一次。一旦我弄清楚了这一点,我只需要再做两个动作:
使用cmdText
编写一个方法,它将允许我执行以下操作:
代码:
static readonly string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
public static void CreateSqlServerUserAccount()
{
string usertobeadded = "admin";
string password = "ComplexPasswordExample123456";
string databasename = "DatabaseNameHere";
string cmdText = "CREATE LOGIN " + usertobeadded + " WITH PASSWORD = '" +
password + "'; USE " + databasename + "; CREATE USER " + usertobeadded + " FOR LOGIN " + usertobeadded + ";";
// The connection is automatically closed at the end of the using block.
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
log.Info("NEW Login & User Account created on Microsoft SQL Server");
}
catch (Exception ex)
{
log.Error("Error creating a new login and user account on Microsoft SQL Server: ", ex);
SystemEvents.DatabaseExceptions(ex);
}
}
}
发布于 2019-06-12 01:39:33
...allow me执行以下操作:
1)更新该用户"admin“的密码
ALTER LOGIN admin WITH PASSWORD = 'newpassword' OLD_PASSWORD = 'oldpassword'
2)删除admin用户账号。
DROP USER [ IF EXISTS ] admin
https://stackoverflow.com/questions/56548873
复制相似问题