更新时间:2023-10-02 12:19:04
清理交易和批次。
DDL语句也支持事务,所以当你创建新的用户,并通过服务器登录,可能不存在这样的登录的服务器上,因为此前的说法是不承诺。
还有一批亲密声明GO将有助于
请先尝试:
CreateServerLogin
问题GO
CreateDBLogin
问题COMMIT
如果失败尝试这样的逻辑:
CreateServerLogin
问题COMMIT
CreateDBLogin
1) If there is already a login 'user1' then I create a user 'user1' for database 'db1' by calling CreateDatabaseUser
function
2) Else, I create a login 'user1' with password 'password1' with default database as 'db1' (only after creating db1) using CreateServerLogin
and then I create user 'user1' for 'db1' using CreateDatabaseUser
Method (2) throws an exception at
newUser.Create();
in CreateDatabaseUser
saying, Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for User 'user1'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. --- > System.Data.SqlClient.SqlException: '[user1]' is not a valid login or you do not have permission.
What could be the problem?
private static Login CreateServerLogin(Server server, string database, string login, string password)
{
var newLogin = new Login(server, login)
{
LoginType = LoginType.SqlLogin,
DefaultDatabase = database,
PasswordPolicyEnforced = false
};
newLogin.Create(password);
return newLogin;
}
private static User CreateDatabaseUser(Database database, string user, string login)
{
var newUser = new User(database, user) { UserType = UserType.SqlLogin, Login = login };
newUser.Create();
newUser.AddToRole("db_owner");
return newUser;
}
check up transactions and batches.
DDL statements also support transactions, so when you create new user and pass server login, there might not be such login on the server because previous statement being not committed.
Also batch close statement "GO" would help
Try first:
CreateServerLogin
issue GO
CreateDBLogin
issue COMMIT
if failed try this logic:
CreateServerLogin
issue COMMIT
CreateDBLogin