为未登录的用户分配登录名 (SQL Server) [英] Assign a login to a user created without login (SQL Server)

问题描述

我的数据库中有一个没有关联登录名的用户.好像是在没有登录的情况下创建的.

I have got a user in my database that hasn't got an associated login. It seems to have been created without login.

每当我尝试使用此用户连接到数据库时，我都会收到以下错误:

Whenever I attempt to connect to the database with this user I get the following error:

Msg 916, Level 14, State 1, Line 1
The server principal "UserName" is not able to access the database 
"DatabaseName" under the current security context.

我想为此用户指定一个登录名，以便我可以实际使用它来访问数据库.我已尝试使用以下脚本将登录名与用户关联.

I'd like to specify a login for this user so that I can actually use it to access the database. I've tried the following script to associate a login with the user.

USE [DatabaseName]
ALTER USER [UserName]
WITH LOGIN = [UserName]

但这给了我以下错误:

Msg 33016, Level 16, State 1, Line 2
The user cannot be remapped to a login. Remapping can only be done for users 
that were mapped to Windows or SQL logins.

有什么办法可以为这个用户分配一个登录名?我不想从头开始，因为这个用户有很多需要重新设置的权限.

Is there any way I can assign a login to this user? I'd like to not have to start from scratch because this user has a lot of permissions that would need setting up again.

针对 Philip Kelley 的问题，这是我运行 select * from sys.database_principals where name = 'username' 时得到的结果.

in response to Philip Kelley's question, here's what I get when I run select * from sys.database_principals where name = 'username'.

抱歉图片的大小，您需要在新标签页中打开它才能正确查看.

Apologies for the size of the image, you'll need to open it in a new tab to view it properly.

编辑 2:

好的，我已经按照 gbn 的建议删除了现有的登录名，我正在使用以下脚本创建一个与用户具有相同 SID 的新登录名.

Ok, I've dropped the existing LOGIN as suggested by gbn, and I'm using the following script to create a new LOGIN with same SID as the user.

CREATE LOGIN [UserName] 
WITH PASSWORD=N'Password1', 
DEFAULT_DATABASE=[DatabaseName], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF, 
SID=0x0105000000000009030000001139F53436663A4CA5B9D5D067A02390

它现在给我以下错误消息，似乎 SID 对于 LOGIN 的 SID 字段来说太长了.

It's now giving me the following error message, it appears that the SID is too long for the LOGIN's SID field.

Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).

我是在没有桨的情况下爬上小溪吗?

Am I up the creek without a paddle?

推荐答案

您有一个孤立用户，并且无法使用 ALTER USER(尚)重新映射，因为没有要映射到的登录名.因此，您需要先运行 CREATE LOGIN.

You have an orphaned user and this can't be remapped with ALTER USER (yet) becauses there is no login to map to. So, you need run CREATE LOGIN first.

如果数据库级用户是

• Windows 登录，映射将通过 AD SID 自动修复
• 一个 SQL 登录，使用 sys.database_principals 中的sid"用于登录的 SID 选项

• a Windows Login, the mapping will be fixed automatcially via the AD SID
• a SQL Login, use "sid" from sys.database_principals for the SID option for the login

然后运行 ​​ALTER USER

Then run ALTER USER

在评论和更新后编辑

来自 sys.database_principals 的 sid 用于 Windows 登录.

The sid from sys.database_principals is for a Windows login.

因此尝试创建并重新映射到 SQL 登录将失败

So trying to create and re-map to a SQL Login will fail

运行此程序以获取 Windows 登录

Run this to get the Windows login

SELECT SUSER_SNAME(0x0105000000000009030000001139F53436663A4CA5B9D5D067A02390)

这篇关于为未登录的用户分配登录名 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！