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.
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