我正在尝试通过 SQL Server Management Studio 访问我的托管服务器的数据库,登录之前的一切都很好,但是当我使用命令 use myDatabase
时,它给了我这个错误:
The server principal "****" is not able to access the database "****" under the current security context.
我搜索了一遍,托管服务提供商列出了针对该问题的 this 修复程序。
但这对我不起作用,可能是因为它适用于 SQL Server Management Studio 2008,但是我使用的是 SQL Server Management Studio 2012。
这会是个问题吗?如果是的话,谁能告诉我它在 SSMS 2012 中的替代方案?
检查您的用户是否映射到您尝试登录的数据库。
我们在 PROD 环境中向 SSRS 部署报告时遇到了同样的错误。发现这个问题甚至可以用“use”语句来重现。解决方案是将用户的 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
SQL 登录是在服务器级别定义的,并且必须映射到特定数据库中的用户。
在 SSMS 对象资源管理器中,在要修改的服务器下,展开 Security > Logins,然后双击相应的登录条目。这将弹出“登录属性”对话框。
选择用户映射,这将显示服务器上的所有数据库。那些已经将用户映射到该登录名的用户将选中“映射”复选框。从这里您可以选择其他数据库(并确保选择用户应属于的每个数据库中的哪些角色),然后单击“确定”添加映射。
请注意,虽然将用户命名为与登录名相同以避免混淆是常见的做法,但它们不必匹配,您可以随意命名用户。
https://i.stack.imgur.com/rv35R.png
在还原或类似操作之后,这些映射可能会断开连接。在这种情况下,用户可能仍然存在于数据库中,但实际上并未映射到登录名。如果发生这种情况,您可以运行以下命令来恢复登录:
USE {database};
ALTER USER {user} WITH login = {login}
您还可以删除 DB 用户并从“登录属性”对话框重新创建它,但任何角色成员资格或其他设置都需要重新创建。
我花了很长时间来解决这个问题,然后我意识到我犯了一个简单的错误,因为我忘记了我的连接目标是哪个特定数据库。我使用标准 SQL Server 连接窗口来输入凭据:
https://i.stack.imgur.com/ezSbf.jpg
我必须检查“连接属性”选项卡以确认我选择了正确的数据库来连接。我不小心将此处的连接到数据库选项设置为上一个会话的选择。这就是为什么我无法连接到我认为我正在尝试连接的数据库的原因。
https://i.stack.imgur.com/XRasS.jpg
请注意,您需要单击 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>';
就我而言,该消息是由无意中将数据库名称包含在“对象名称”中的同义词引起的。当我以新名称恢复数据库时,同义词仍指向旧数据库名称。由于用户在旧数据库中没有权限,因此出现了该消息。为了解决这个问题,我删除并重新创建了同义词,而不用数据库名称限定对象名称:
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
我相信您在创建数据库用户时可能缺少“Grant Connect To”语句。
下面是完整的片段,您需要创建针对 SQL Server 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
在解决我的边缘案例问题之前,没有一个出色的答案。在我的例子中,“以用户身份执行”语句在调用执行存储过程之前,但该过程是从不同数据库中的表中读取的。即使用户是系统管理员,存储过程也因“在当前安全上下文下”无法访问第二个数据库而失败。这在生产中有效,但在我们的开发环境中失败了。我看到在生产中,可信任在初始数据库中设置为“开启”,但在开发中关闭该数据库。我读到在恢复数据库时(当我们不时将生产数据库恢复到我们的开发环境时)具有关闭可信赖的效果。在 dev 中将其设置为“on”允许用户对第二个数据库进行读取访问。
即使用户已正确映射到登录名,我们也遇到了同样的错误。
在尝试删除该用户后,发现一些 SP 包含该用户的“执行方式”。
该问题已通过删除这些 SP、删除用户、重新创建链接到登录的用户以及重新创建 SP 来解决。
可能它是从备份恢复(在相关登录不存在期间)或批量模式同步(如果它可以创建一个带有执行的 SP 的,即使用户不存在)。也可能有与 this answer 相关。
我在 vb.net 中使用服务器管理对象 (SMO) 时遇到了同样的错误(我确信它在 C# 中是一样的)
Techie Joe 对最初帖子的评论是一个有用的警告,即在共享托管中会发生许多其他事情。花了一点时间才弄明白,但下面的代码显示了访问 SQL 数据库的方式必须非常具体。每当 SMO 调用在共享托管环境中不精确时,似乎就会出现“服务器主体...”错误。
第一部分代码针对本地 SQL Express 服务器,并依赖于简单的 Windows 身份验证。这些示例中使用的所有代码均基于 Robert Kanasz 在此 Code Project website article 中的 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,我认为其他新手可能会很高兴知道这个错误还有一个更简单的解释——我们只是把它编码错了。
在 SQL 2017 上 - 数据库 A 具有数据库 B 的同义词。用户可以连接到数据库 A,并且对指向 B 的同义词的 sp(在 A 上)拥有执行权限。用户设置了连接访问权限 B。仅当将 CONNECT 授予数据库 B 的公共组执行了 A 上的 sp 工作。我不记得在 2012 年以这种方式工作,因为授予用户连接似乎只起作用。
我遇到了一个特定于没有登录的用户的问题,在将数据库备份并还原到另一台服务器后,用户失去了与数据库的连接。
为了解决这个问题,我们需要确保用户已连接到数据库。
GRANT CONNECT TO [DatabaseUser]
GO
这类似于上面 Salim Gangji 给出的答案,但特定于没有登录的用户的情况。
我用:
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
这是因为您的服务器未映射到您要访问的所需数据库。
以下为我工作:
转到服务器中的安全文件夹。
双击它并转到登录文件夹。
找到您的用户 ID 并双击它。
登录属性窗口将打开。
在那个去用户映射。
勾选您要使用该登录名映射的所有数据库。
不定期副业成功案例分享