what permissions does user need to have to use linked server and its default remote user mapping
1
vote
0
answers
245
views
i have some linkedserver and for it created default maping that
mapping list
is empty
and all goes thrue 'Be made using this seciurity context'
and this works fine - i can querry this linked from any user that have sysadmin
role
but when i have user that is public
only
then i get that failed - 'there is no mapping exists for user x'
how can i fix that? is there some special permission that i need to apply ?
it is MSSQL 2019
scripted linked is:
EXEC master.dbo.sp_addlinkedserver @server = N'PB01_ENC', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'x.x.x.x,9945'@provstr=N'Encrypt=yes;TrustServerCertificate=yes;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PB01_ENC',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'remote proc transaction promotion', @optvalue=N'true'
others linked but not encrypted works fine
am i missing something here maybe?
even if i add specyfic mapping for this public user - with password or via impersonate - stil the same :/
please advice
best regards !
Asked by Dorian
(113 rep)
Mar 16, 2023, 11:34 AM
Last activity: Mar 16, 2023, 12:43 PM
Last activity: Mar 16, 2023, 12:43 PM