SQL Server脚本创建一个新用户 [英] SQL Server Script to create a new user

问题描述

我想写一个脚本在SQL Server Express中创建一个管理员用户（使用abcd密码）。
此外，我想分配此用户（管理员）的完全权限

解决方案

根据您的问题，可能对于用户和登录之间的区别有点困惑。 是整个SQL Server上的帐户 - 能够登录到服务器并且具有密码的用户。 >很容易，并且必须（显然）在为特定数据库中的登录创建用户帐户之前完成：

  CREATE LOGIN NewAdminName WITH PASSWORD ='ABCD'
 GO 
  

您可以使用刚刚声明的登录创建具有db_owner权限的用户：

  Use YourDatabase; 
 GO 
 
如果不存在（SELECT * FROM sys.database_principals WHERE name = N'NewAdminName'）
 BEGIN 
 CREATE USER [NewAdminName] FOR LOGIN [NewAdminName] 
 EXEC sp_addrolemember N'db_owner'，N'NewAdminName'
 END; 
 GO 
  

现在，登录我把它看上面。例如，在安装数据库时，会为Windows管理员帐户自动创建登录帐户（在大多数SQL Server安装中）。在大多数情况下，我只是使用，当我管理数据库（它有所有权限）。

但是，如果您要从应用程序访问SQL Server，那么您将需要为混合模式设置服务器（包括Windows和SQL登录），并创建如上所示的登录。然后，您将根据您的应用程序需要，授予权限到该SQL登录。有关详情，请参见此处。

UPDATE：Aaron指出使用sp_addsrvrole成员将一个准备好的角色分配给您的登录帐户。这是一个好主意 - 比手动授予权限更快更容易。如果你google的话，你会看到很多链接。但是，您仍然必须了解登录和用户之间的区别。

I want to write a script to create a admin user ( with abcd password )in SQL Server Express. Also I want to assign this user (admin) full rights

解决方案

Based on your question, I think that you may be a bit confused about the difference between a User and a Login. A Login is an account on the SQL Server as a whole - someone who is able to log in to the server and who has a password. A User is a Login with access to a specific database.

Creating a Login is easy and must (obviously) be done before creating a User account for the login in a specific database:

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD'
GO

Here is how you create a User with db_owner privileges using the Login you just declared:

Use YourDatabase;
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewAdminName')
BEGIN
    CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]
    EXEC sp_addrolemember N'db_owner', N'NewAdminName'
END;
GO

Now, Logins are a bit more fluid than I make it seem above. For example, a Login account is automatically created (in most SQL Server installations) for the Windows Administrator account when the database is installed. In most situations, I just use that when I am administering a database (it has all privileges).

However, if you are going to be accessing the SQL Server from an application, then you will want to set the server up for "Mixed Mode" (both Windows and SQL logins) and create a Login as shown above. You'll then "GRANT" priviliges to that SQL Login based on what is needed for your app. See here for more information.

UPDATE: Aaron points out the use of the sp_addsrvrolemember to assign a prepared role to your login account. This is a good idea - faster and easier than manually granting privileges. If you google it you'll see plenty of links. However, you must still understand the distinction between a login and a user.

这篇关于SQL Server脚本创建一个新用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！