在Azure SQL托管实例中db_owner无法访问数据库 [英] Database is not accessible for db_owner in Azure SQL Managed Instance
问题描述
我创建了一个新用户，以使用db_owner权限访问用户数据库.但是，在授予db_owner权限后，用户仍会获得无法通过SSMS访问数据库(对象资源管理器).
I had created a new user to access a user DB with db_owner permission. However, after giving the db_owner permission, the user is still getting "The database is not accessible (Object Explorer) via SSMS.
我能够对Azure SQL DB(弹性池)执行相同的操作，但不适用于Azure SQL MI.
I was able to do the same for Azure SQL DB (elastic pool) but it is not working for Azure SQL MI.
-- create SQL auth login from master
CREATE LOGIN user1
WITH PASSWORD = '@@dsafds12' ;
--Create User from Login in user DB (MYDB)
CREATE USER user1
FROM LOGIN user1
;
--Add user to role member in user DB (MYDB)
EXEC sp_addrolemember N'db_owner', user1
我确实验证了新创建的用户是否具有db_owner权限，但不确定为什么它无法访问数据库.
I did verify that the newly created user has the db_owner permission but not sure why it is not able to access the database.
M.乔杜里
推荐答案
你好，
Hello,
If you try to access the Managed Instance from a SSMS installed on VM on the same network you will be able to connect/authenticate
successfully..
If you try to connect from a SSMS installed on a host located on-premises, on the routing table that is added to the subnet where SQL Managed Instance is deployed, add another route to the routing table for your on premise subnet with next hop Virtual Network Gateway, After that, you will be able to connect from SSMS.
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com
这篇关于在Azure SQL托管实例中db_owner无法访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
