不使用 SMO 将登录名和用户添加到 SQL Server
在我们公司的产品中,我们将数据库附加到SQL Server,并且需要将某些帐户的凭据添加到目录中。
目前我们正在使用 SMO 来执行此操作,但这给我们带来了问题(x86 与 x64、SQL Server 2005 与 2008 等)
是否可以使用 SQL 添加登录名(到服务器)和用户(到目录) ?
这是添加登录名:
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
//Drop existing login (if exist)
Login login = server.Logins[AccountName];
if (login != null)
login.Drop();
//Create new login
login = new Login(server, AccountName);
login.LoginType = LoginType.WindowsUser;
login.Create();
DatabaseMapping mapping = new DatabaseMapping(login.Name, connection.DatabaseName, login.Name);
和添加用户:的
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[CatalogName];
User user = server.Databases[CatalogName].Users[LoginName];
if (user != null)
user.Drop();
user = new User(database, LoginName);
user.Login = LoginName;
user.Create();
user.AddToRole("db_owner");
当前代码,我想使用 SQL 执行这些操作。有什么想法吗?
in our company's product, we attach a database to SQL Server, and we need to add credentials to some accounts to the catalog.
Currently we're using SMO to do this, but this gives us problems (x86 vs x64, SQL Server 2005 vs 2008, etc)
Is there's anyway of adding a login (to the server) and a user (to the catalog) using SQL?
This is the current code to add login:
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
//Drop existing login (if exist)
Login login = server.Logins[AccountName];
if (login != null)
login.Drop();
//Create new login
login = new Login(server, AccountName);
login.LoginType = LoginType.WindowsUser;
login.Create();
DatabaseMapping mapping = new DatabaseMapping(login.Name, connection.DatabaseName, login.Name);
and to add user:
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[CatalogName];
User user = server.Databases[CatalogName].Users[LoginName];
if (user != null)
user.Drop();
user = new User(database, LoginName);
user.Login = LoginName;
user.Create();
user.AddToRole("db_owner");
I want to perform these operations using SQL. Any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
创建登录 和 创建用户
测试 首先登录:
测试 用户优先
添加用户到角色:
仅供参考,SMO 复制 SQL 功能。我不认为有什么事情只能通过 SMO 来完成(当然有很多只使用 SQL 的东西)。
CREATE LOGIN and CREATE USER
To test for a login first:
To test for a user first
To add user to role:
FYI, SMO replicates SQL functionality. I don't think there is anything that can be done only SMO (there is plenty of SQL only stuff of course).