在SQL Server 2005中创建新的db用户

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*;

6 个解决方案

#1

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=OFF switches off password complexity check, etc

其中CHECK_POLICY = OFF关闭密码复杂性检查等

#2

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
创建一个整体的“登录”到您的SQL Server
create users for this login in each database needed
在所需的每个数据库中为此登录创建用户

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

#3

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.
如果可能的话，您希望使用Windows身份验证，而不是基于SQL Server的登录（这是您以这种方式使用user / pwd时正在执行的操作）。如果从与SQL Server相同的域中的计算机上运行，则可以使用作为Windows用户帐户的服务帐户。这可确保域是安全性的唯一来源。
You didn't say what rights the user needed. Avoid using db_datareader and db_datawriter roles whenever possible. They give IMPLICIT access 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.
您没有说明用户需要什么权利。尽可能避免使用db_datareader和db_datawriter角色。它们提供对表和视图的IMPLICIT访问权限，如果有人对数据库执行快速权限检查，他们可能不会考虑检查这些角色的成员身份。这意味着您的安全报告正在使用。最佳实践是创建自己的数据库角色，为其分配权限，并使用户成为该角色的成员。
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.
尽可能使用强密码。一个示例关闭了密码策略。 SQL Server将使用本地服务器的密码策略（通常在域级别设置）。如果可能，您希望维护强密码策略。

#4

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

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

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"

您必须确保您的数据库配置了用户身份验证和SQL身份验证如果使用Management Studio：右键单击服务器，选择“安全”确保服务器身份验证是“SQL Server和Windows身份验证模式”
in Security-logins, right click and select "New Login", select SQL Authentication, use the username and password you like.

在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 
```
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):

在您想要的数据库上，在安全性，用户中，选择新用户。选择用户名，并附加刚刚创建的登录名，然后选择要应用于此用户的角色（即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.

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

#5

CREATE LOGIN MyNewUser WITH PASSWORD = 'top$secret'

USE AdventureWorks 
CREATE USER MyNewUser FOR LOGIN MyNewUser 
GO

#6

-1

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'

#1

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