如何让 SQLServerExpress 创建用户？

发布于 2024-09-04 10:52:46 字数 284 浏览 4 评论 0原文

在我的计算机上以管理员身份运行，我可以很好地创建数据库，但是当我尝试创建用户时，它会失败并显示此错误消息。

1> create user mark from login mark
2> go
Msg 15007, Level 16, State 1, Server ENDER\SQLEXPRESS, Line 1
'mark' is not a valid login or you do not have permission.

我正在使用 sqlcmd 来执行此操作。

原文

Running as Administrator on my machine, I can create databases just fine, but when I try to create a user it fails with this error message.

1> create user mark from login mark
2> go
Msg 15007, Level 16, State 1, Server ENDER\SQLEXPRESS, Line 1
'mark' is not a valid login or you do not have permission.

I'm using sqlcmd to do this.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少年亿悲伤 2024-09-11 10:52:46

您需要在创建数据库用户“标记”之前创建服务器登录“标记”，

例如对于 Windows 身份验证

CREATE LOGIN [mark] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

，例如或者对于 SQL Server 身份验证

CREATE LOGIN [mark] WITH PASSWORD=N'YourPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

然后，一旦创建登录名，以下操作应该可以工作

CREATE USER [mark] FOR LOGIN [mark]

顺便说一下，获取服务器登录名列表，这是 Management Studio 使用的查询

SELECT
log.name AS [Name],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
log.is_disabled AS [IsDisabled],
log.create_date AS [CreateDate]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC

You need to create the server login "mark" before creating a database user "mark"

e.g. For Windows Authentication

CREATE LOGIN [mark] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

e.g. Or For SQL Server Authentication

CREATE LOGIN [mark] WITH PASSWORD=N'YourPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Then once the login is created the following should work

CREATE USER [mark] FOR LOGIN [mark]

By the way to get a list of server logins this is the query that management studio uses

SELECT
log.name AS [Name],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
log.is_disabled AS [IsDisabled],
log.create_date AS [CreateDate]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC

回复收藏 0 原文

~没有更多了~