在 SQL Server 2005 中创建一个新的数据库用户

Create a new db user in SQL Server 2005

提问by raklos

how do you create a new database user with password in sql server 2005?

你如何在 sql server 2005 中创建一个带有密码的新数据库用户？

i will need this user/password to use in the connection string eg:

我将需要此用户/密码才能在连接字符串中使用，例如：

uid=*user*;pwd=*password*;

回答by abatishchev

CREATE LOGIN [user] WITH PASSWORD='password', 
       DEFAULT_DATABASE=[your_db], CHECK_POLICY=OFF
GO

CREATE USER [user] FOR LOGIN [user]
EXEC sp_addrolemember N'db_datareader', N'your_db'
EXEC sp_addrolemember N'db_datawriter', N'your_db'
GO

Where CHECK_POLICY=OFFswitches off password complexity check, etc

在哪里CHECK_POLICY=OFF关闭密码复杂性检查等

回答by marc_s

As of SQL Server 2005, you should basically create users in two steps:

从 SQL Server 2005 开始，您基本上应该分两步创建用户：

• create a "login" to your SQL Server as a whole
• create users for this login in each database needed

• 为整个 SQL Server 创建一个“登录”
• 在需要的每个数据库中为此登录创建用户

You'd go about doing this like so:

你会这样做：

CREATE LOGIN MyNewUser WITH PASSWORD = 'top$secret';

And the "USE" your database and create a user for that login:

然后“使用”您的数据库并为该登录创建一个用户：

USE AdventureWorks;
CREATE USER MyNewUser FOR LOGIN MyNewUser

回答by K. Brian Kelley

As indicated, use the CREATE LOGIN to create the ability to connect to SQL Server as that account. Then use CREATE USER within the database to give that login the ability to access the database in question.

如上所述，使用 CREATE LOGIN 创建以该帐户身份连接到 SQL Server 的能力。然后在数据库中使用 CREATE USER 为该登录提供访问相关数据库的能力。

However, a few security points based on some of these comments:

但是，基于其中一些评论的一些安全点：

• If at all possible, you want to use Windows authentication, not a SQL Server based login (which is what you are doing when you use user/pwd in this manner). If you are running from a computer on the same domain as SQL Server, you can use a service account that is a Windows user account. This ensures the domain is the single source for security.
• You didn't say what rights the user needed. Avoid using db_datareader and db_datawriter roles whenever possible. They give IMPLICITaccess to tables and views and if someone is performing a quick permissions check on the database, they may not think to check the membership in these roles. That means your reporting on security is using. Best practices say to create your own database role, assign permissions to it, and make the user a member of that role.
• Whenever possible, use a strong password. One example had the password policies turned off. SQL Server will use the password policy from the local server (which is usually set at the domain level). You want to maintain that strong password policy, if possible.

• 如果可能，您希望使用 Windows 身份验证，而不是基于 SQL Server 的登录（这是您以这种方式使用 user/pwd 时所做的）。如果从与 SQL Server 位于同一域的计算机上运行，​​则可以使用作为 Windows 用户帐户的服务帐户。这确保了域是安全的唯一来源。
• 你没有说用户需要什么权限。尽可能避免使用 db_datareader 和 db_datawriter 角色。他们授予对表和视图的IMPLICIT访问权限，如果有人对数据库执行快速权限检查，他们可能不会考虑检查这些角色的成员资格。这意味着您的安全报告正在使用。最佳实践是创建您自己的数据库角色，为其分配权限，并使用户成为该角色的成员。
• 尽可能使用强密码。一个例子是关闭了密码策略。SQL Server 将使用来自本地服务器的密码策略（通常在域级别设置）。如果可能，您希望维护该强密码策略。

回答by Nicolas Irisarri

You'll have to create it first as a user, and then set up the correct permissions for the user.

您必须首先以用户身份创建它，然后为用户设置正确的权限。

1. you'll have to ensure that your DB is configured with both User auth and SQL auth If using the Management Studio: right-click on the Server, select "Security" ensure that server authentication is "SQL Server and Windows Authentication mode"

2. in Security-logins, right click and select "New Login", select SQL Authentication, use the username and password you like.

   USE [master]
   GO
   CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   GO 
   

3. on the DB you want, in security, users, select new User. Select a username, and attach the login name you've just created, and select the roles you want to apply to this user (i.e. db_datareader, db_datawriter):

   USE [MY_DATABASE]
   GO
   CREATE USER [myDefaultUser] FOR LOGIN [ test]
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
   GO
   

1. 您必须确保您的数据库配置了用户身份验证和 SQL 身份验证如果使用 Management Studio：右键单击服务器，选择“安全”确保服务器身份验证为“SQL Server 和 Windows 身份验证模式”

2. 在 Security-logins 中，右键单击并选择“New Login”，选择 SQL Authentication，使用您喜欢的用户名和密码。

   USE [master]
   GO
   CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   GO 
   

3. 在你想要的数据库上，在安全，用户中，选择新用户。选择一个用户名，并附上您刚刚创建的登录名，然后选择您要应用到该用户的角色（即db_datareader, db_datawriter）：

   USE [MY_DATABASE]
   GO
   CREATE USER [myDefaultUser] FOR LOGIN [ test]
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
   GO
   USE [MY_DATABASE]
   GO
   EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
   GO
   

That is it. Now you can create your connection string using this password.

这就对了。现在您可以使用此密码创建连接字符串。

回答by Prashant

CREATE LOGIN MyNewUser WITH PASSWORD = 'top$secret'

USE AdventureWorks 
CREATE USER MyNewUser FOR LOGIN MyNewUser 
GO

回答by Oscar Cabrero

USE [MASTER]


EXEC master.dbo.sp_addlogin @loginame = N'USERNAME', @passwd = 'THEPASS' @defdb = N'master', @deflanguage = N'us_english'


USE [YOUR_DB]
    EXEC dbo.sp_grantdbaccess @loginame = N'USERNAME', @name_in_db = N'USERNAME'