User not see SQL Server instance in SSMS Object Explorer, but can select from available database dropdown and can query against the database

myss37ts 于 2023-11-16 发布在 SQL Server

关注(0)|答案(1)|浏览(152)

I'm having an issue with a user that is trying to work with an instance of SQL Server using SSMS. When they try to connect, they get the error below, but they are then able to select the database from the available databases dropdown list and they can run a select statement against the database. Unfortunately, they cannot see the database listed in object explorer. This is on SQL Server 2022 using SSMS 19. The user is able to connect to and see other servers, just not this particular instance.
The EXECUTE permission was denied on the object 'xp_msver', database 'mssqlsystemresource', schema 'sys'.

I have tried deleting the user's login and re-adding it with full permissions. I have also tried granting execute on sp.xp_msver to [user], but I received the error message:

Cannot find the object 'xp_msver', because it does not exist or you do not have permission.

sql-server

来源：https://stackoverflow.com/questions/77440982/user-not-see-sql-server-instance-in-ssms-object-explorer-but-can-select-from-av

1条答案

按热度按时间

rmbxnbpk1#

Verify that public in master has execute permissions on the proc:

select p.name, perm.*
from sys.database_permissions perm
join sys.database_principals p
  on p.principal_id = perm.grantee_principal_id
where perm.major_id = object_id('sys.xp_msver')

should output

name                  class class_desc          major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name 
--------------------- ----- ------------------- ----------- ----------- -------------------- -------------------- ---- ----------------
public                1     OBJECT_OR_COLUMN    -524454186  0           0                    1                    EX   EXECUTE

赞(0）回复(0）举报 2023-11-16

我来回答

User not see SQL Server instance in SSMS Object Explorer, but can select from available database dropdown and can query against the database

1条答案

相关问题

热门标签

最新问答