服务器主体无法在 SQLServerMS2012中的当前安全上下文下访问数据库

小开

最佳答案

检查用户是否映射到要登录的数据库。

小开

我们在 PROD 环境中将报告部署到 SSRS 时也出现了同样的错误。结果发现，这个问题甚至可以通过一个“使用”声明再现出来。解决方案是将用户的 GUID 帐户引用与有问题的数据库重新同步(例如，使用“ sp _ change _ users _ login”，就像还原数据库之后那样)。附加了一个股票(光标驱动)脚本来重新同步所有帐户:

USE <your database>
GO


-------- Reset SQL user account guids ---------------------
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = su.name
FROM sysusers su
JOIN sys.server_principals sp ON sp.name = su.name
WHERE issqluser = 1 AND
(su.sid IS NOT NULL AND su.sid <> 0x0) AND
suser_sname(su.sid) is null
ORDER BY su.name


OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName


WHILE (@@fetch_status = 0)
BEGIN
--PRINT @UserName + ' user name being resynced'
exec sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END


CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur

小开

在我的例子中，消息是由一个同义词引起的，该同义词无意中在“对象名称”中包含了数据库名称。当我使用新名称还原数据库时，同义词仍然指向旧的 DB 名称。由于用户在旧数据库中没有权限，因此出现了消息。为了修复这个问题，我删除并重新创建了同义词，而没有使用数据库名称限定对象名称:

    USE [new_db]
GO


/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
DROP SYNONYM [dbo].[synTable]
GO


/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
CREATE SYNONYM [dbo].[synTable] FOR [dbo].[tTheRealTable]
GO

小开

我在 vb.net 中使用服务器管理对象(Server Management Objects，SMO)时遇到了同样的错误(我确信在 C # 中也是如此)

Techie Joe 在最初的帖子上的评论是一个有用的警告，在共享主机中有许多额外的事情正在发生。虽然花了一些时间才弄明白，但下面的代码显示了访问 SQL 数据库的方式必须非常具体。每当 SMO 调用在共享主机环境中不是特定的时候，就会出现‘ server main...’错误。

第一部分代码针对本地 SQLExpress 服务器，并依赖于简单的 Windows 身份验证。这些示例中使用的所有代码都基于 Robert Kanasz 在此代码项目网站文章中的 SMO 教程:

  Dim conn2 = New ServerConnection()
conn2.ServerInstance = "<local pc name>\SQLEXPRESS"
Try
Dim testConnection As New Server(conn2)
Debug.WriteLine("Server: " + testConnection.Name)
Debug.WriteLine("Edition: " + testConnection.Information.Edition)
Debug.WriteLine(" ")


For Each db2 As Database In testConnection.Databases
Debug.Write(db2.Name & " - ")
For Each fg As FileGroup In db2.FileGroups
Debug.Write(fg.Name & " - ")
For Each df As DataFile In fg.Files
Debug.WriteLine(df.Name + " - " + df.FileName)
Next
Next
Next
conn2.Disconnect()


Catch err As Exception
Debug.WriteLine(err.Message)
End Try

上面的代码找到。本地 SQLEXPRESS 服务器上每个数据库的 mdf 文件都很好，因为身份验证是由 Windows 处理的，而且所有数据库都有广泛的身份验证。

在下面的代码中，有2个部分迭代。Mdf 文件。在这种情况下，只有查找文件组的第一次迭代可以工作，而且它只能找到一个文件，因为连接只连接到共享宿主环境中的一个数据库。

第二次迭代，也就是上一次迭代的一个副本，会立即窒息，因为它的编写方式试图访问共享环境中的第一个数据库，而用户 ID/密码并不适用于这个环境，因此 SQL 服务器返回一个授权错误，其形式为“服务器主体...”错误。

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection
sqlConnection1.ConnectionString = "connection string with User ID/Password to a specific database in a shared hosting system. This string will likely also include the Data Source and Initial Catalog parameters"
Dim conn1 As New ServerConnection(sqlConnection1)
Try
Dim testConnection As New Server(conn1)
Debug.WriteLine("Server: " + testConnection.Name)
Debug.WriteLine("Edition: " + testConnection.Information.Edition)
Debug.WriteLine(" ")


Dim db2 = testConnection.Databases("the name of the database to which the User ID/Password in the connection string applies")
For Each fg As FileGroup In db2.FileGroups
Debug.Write(fg.Name & " - ")
For Each df As DataFile In fg.Files
Debug.WriteLine(df.Name + " - " + df.FileName)
Next
Next


For Each db3 As Database In testConnection.Databases
Debug.Write(db3.Name & " - ")
For Each fg As FileGroup In db3.FileGroups
Debug.Write(fg.Name & " - ")
For Each df As DataFile In fg.Files
Debug.WriteLine(df.Name + " - " + df.FileName)
Next
Next
Next


conn1.Disconnect()


Catch err As Exception
Debug.WriteLine(err.Message)
End Try

在第二个迭代循环中，代码编译得很好，但是由于没有设置 SMO 来使用精确的语法访问正确的数据库，这种尝试失败了。

因为我正在学习 SMO，我想其他新手可能会喜欢知道还有一个更简单的解释这个错误-我们只是编码错误。

小开

即使用户被正确地映射到登录名，我们还是出现了相同的错误。

在尝试删除该用户之后，发现一些 SP 包含该用户的“ with execute as”。

通过删除这些 SP、删除用户、重新创建链接到登录的用户以及重新创建 SP，问题得到了解决。

它可能是从备份恢复(在相关登录不存在的时候)或批量模式同步(如果可能的话，即使用户不存在，也可以创建一个带执行的 SP)中进入这种状态的。也可能与这个答案有关。

小开

我花了相当长的时间来解决这个问题，然后我意识到我犯了一个简单的错误，事实上我忘记了我的连接的目标是哪个特定的数据库。我使用标准的 SQLServer 连接窗口输入凭据:

我必须检查 连接属性选项卡来验证我选择的连接数据库是否正确。我不小心将这里的 连接到数据库选项设置为上一个会话的选项。这就是为什么我无法连接到数据库，我思考试图连接到。

注意，需要单击 Options >>按钮才能显示 连接属性和其他选项卡。

小开

这对我很有效:

use <Database>
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='<userLogin>',@LoginName='<userLogin>';

这个问题可以通过以下方式看到:

SELECT sid FROM sys.sysusers WHERE name = '<userLogin>'
SELECT sid FROM sys.syslogins WHERE name = '<userLogin>';

小开

SQL登录名定义在 服务器级(而不是数据库级) ，并且必须在特定的数据库中映射到用户。

在 SSMS 对象资源管理器中，在要修改的 服务器下，展开保安 > 登录名，然后双击相应的登录条目。这将打开“ LoginProperties”对话框。

选择 用户映射，它将显示服务器上的所有数据库。那些已经将用户映射到该登录的用户将选中“ Map”复选框。从这里可以选择其他数据库(并确保选择用户应该属于每个数据库中的哪些角色) ，然后单击好的添加映射。

注意，为了避免混淆，通常的做法是将用户命名为与 Login 相同的用户，但是它们不必匹配，您可以根据自己的喜好为用户命名。

在还原或类似操作之后，这些映射可能会断开连接。在这种情况下，用户可能仍然存在于数据库中，但实际上没有映射到登录名。如果发生这种情况，您可以运行以下命令来恢复登录:

USE {database};
ALTER USER {user} WITH login = {login}

您还可以删除 DB 用户并从 Login Properties 对话框中重新创建它，但是需要重新创建任何角色成员关系或其他设置。

小开

我相信您在创建数据库用户时可能遗漏了“ Grant Connect To”语句。

下面是您需要创建针对 SQLServer DBMS 的登录名和针对数据库的用户的完整代码片段

USE [master]
GO


CREATE LOGIN [SqlServerLogin] WITH PASSWORD=N'Passwordxyz', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


USE [myDatabase]
GO


CREATE USER [DatabaseUser] FOR LOGIN [SqlServerLogin] WITH DEFAULT_SCHEMA=[mySchema]
GO


GRANT CONNECT TO [DatabaseUser]
GO


-- the role membership below will allow you to run a test "select" query against the tables in your database
ALTER ROLE [db_datareader] ADD MEMBER [DatabaseUser]
GO

小开

在 SQL 2017上-数据库 A 与数据库 B 有同义词。用户可以连接到数据库 A，并且对指向数据库 B 的同义词 sp (On A)拥有执行权限。只有当向数据库 B 的公共组授予 CONNECT 时，数据库 A 上的 sp 才起作用。我不记得这种工作方式在2012年授予连接给用户似乎只有工作。

小开

我有这个问题特定的情况下的用户没有 LOGIN，后备份和恢复数据库到一个不同的服务器，用户失去了与数据库的连接。

为了解决这个问题，我们需要确保用户连接到数据库。

GRANT CONNECT TO [DatabaseUser]
GO

这与上面 Salim Gangji 给出的答案类似，但是特定于“没有 LOGIN 的用户”的情况。

小开

我用:

 DECLARE @sql VARCHAR(255)
DECLARE @owner VARCHAR(255)


WHILE EXISTS (SELECT DISTINCT S.name AS owner
FROM sys.schemas s, sys.database_principals u
WHERE s.principal_id = u.principal_id
AND u.name NOT IN( 'dbo' ,'guest','sys','INFORMATION_SCHEMA')
AND u.name NOT LIKE 'db_%')
BEGIN
SET @owner = (SELECT DISTINCT TOP(1) s.name
FROM sys.schemas s, sys.database_principals u
WHERE s.principal_id = u.principal_id
AND u.name NOT IN( 'dbo' ,'guest','sys','INFORMATION_SCHEMA')
AND u.name NOT LIKE 'db_%')
SET @sql = 'ALTER AUTHORIZATION ON SCHEMA::' + @owner + ' TO dbo'
PRINT @sql
exec (@sql)
END




DECLARE @name varchar(500)
DECLARE @db varchar(100)= DB_NAME()
DECLARE @strQuery varchar(1000)='use '+ @db
DECLARE consec CURSOR FOR
select name from sys.sysusers WHERE hasdbaccess=1 and name not in ('dbo','guest') /*and name not like 'esfcoah%'*/ AND status=0
OPEN consec
FETCH NEXT FROM consec INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @strQuery='use ['+@db+']'
exec(@strQuery)
if exists(select * from sys.schemas where name like @name)
begin
set @strQuery='DROP SCHEMA ['
set @strQuery=@strQuery+@name+']'
exec(@strQuery)
end


set @strQuery='DROP USER ['
set @strQuery=@strQuery+@name+']'
exec(@strQuery)


set @strQuery='USE [master]'
exec (@strQuery)


if not exists(select * from sys.syslogins where name like @name)
begin
set @strQuery='CREATE LOGIN ['+@name+'] WITH PASSWORD=N''a'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
exec (@strQuery)
end


set @strQuery='use ['+@db+']'
exec(@strQuery)


set @strQuery='CREATE USER ['+@name+'] FOR LOGIN ['+@name+']'
exec(@strQuery)


set @strQuery='EXEC sp_addrolemember N''db_owner'', N'''+@name+''''
exec(@strQuery)


FETCH NEXT FROM consec INTO @name
end
close consec
deallocate consec

小开

在这个案子解决我的边缘案件问题之前，没有一个好答案。在我的例子中，在调用执行存储过程之前有一个“ Executeasuser”语句，但该过程是从不同数据库中的表中读取的。即使用户是 sysadmin，sproc 也会因为“在当前安全上下文下”无法访问第二个数据库而失败这在生产环境中起作用，但在我们的开发环境中失败了。我看到在生产环境中，值得信赖的在最初的数据库中设置为“打开”，但在开发过程中关闭了该数据库。我了解到，当还原数据库时(当我们不时地将生产 DBs 还原到开发环境中时)会产生关闭值得信赖的效果。在 dev 中将其设置为“ on”允许用户对第二个数据库进行读访问。

小开

这是因为您的服务器没有映射到要访问的所需数据库。

下面这句话对我很有用:

转到服务器中的“安全”文件夹。
双击它，进入 Logins 文件夹。
找到您的用户 ID 并双击它。
“登录属性”窗口将打开。
进入用户映射。
勾选要使用该登录名映射的所有数据库。