如何在我添加到项目的SQL Server Express数据库中创建用户?

How can I create a SQL user in a SQL Server Express database that I added to my project?

如何在添加到项目的SQL Server Express数据库中创建SQL用户?

I need to create a user to use in a connection string that doesn't use Integrated Security.

我需要创建一个用户来在不使用集成安全性的连接字符串中使用。

2 个解决方案

#1

You would need to create a SQL Authenticated login first with CREATE LOGIN then add a user associated with that login to your database by using CREATE USER.

您需要先创建一个SQL已验证登录，然后使用create user将与该登录相关联的用户添加到数据库中。

USE [master]
GO
CREATE LOGIN [JohnEgbert] WITH PASSWORD=N'YourPassword', 
                 DEFAULT_DATABASE=[YourDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [YourDB]
GO
CREATE USER [JohnEgbert] FOR LOGIN [JohnEgbert] WITH DEFAULT_SCHEMA=[dbo]
GO

#2

If you create a SQL login and a SQL user without errors, but then get an error when trying to connect, you may have the SQL Authentication mode disabled. To check, run:

如果您创建了一个SQL登录和一个没有错误的SQL用户，但是在尝试连接时出现了错误，那么可能会禁用SQL身份验证模式。检查,运行:

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

If this returns 1, then SQL Authentication (mixed mode) is disabled. You can change this setting using SSMS, regedit, or T-SQL:

如果返回1，则禁用SQL身份验证(混合模式)。您可以使用SSMS、regedit或T-SQL更改此设置:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Then restart the SQL Server service, and create a login and a user, here with full permissions:

然后重新启动SQL Server服务，并创建一个登录名和一个用户，这里有完整的权限:

CREATE LOGIN myusername WITH PASSWORD=N'mypassword', 
                 DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember 'myusername', 'sysadmin'
CREATE USER myusername FOR LOGIN myusername WITH DEFAULT_SCHEMA=[dbo]

#1