Re: deny select on linked server

Here is a way to disable one login from using the linked server, while allowing all other logins through.

USE [master]
-- Grant everyone to use their own credentials
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLink', @locallogin = NULL , @useself = N'True'
-- Grant the excluded login rights to login with a non-existing login and password
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLink, @locallogin = N'Domain\ExcludedLogin', @useself = N'False', @rmtuser = N'xyzzy', @rmtpassword = N'xyzzy'

When Domain\ExcludedLogin attempts to use the MyLink linked server it will get the error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'xyzzy'.

Of course, you can name the login something better than xyzzy.

To do this through SQL Server Management Studio (or Enterprise Manager) you go the Security pane of the linked server and do the following:

1. In the top grid add the login to be excluded to the "Local server login to remote server login mappings:" giving it the false username and password.

2. In the radio buttons below, select "Be made using the login's current security context"

All the best,
RLF

"Selvad71" <selvad71@xxxxxxxxx> wrote in message news:edf03794-b099-447e-8984-eed977b17940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 25 Nov, 11:29, "Uri Dimant" <u...@xxxxxxxxxxx> wrote:
Create a stored procedure which executes Linked server query and DENY
EXECUTE permission to this user.
Use must be sysadmin to alter /see linked servers

"Selvad71" <selva...@xxxxxxxxx> wrote in message

news:caa1c739-073e-4d1d-ad1a-be5d1de5a62e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



> Hi,
> i've a linked server. i force security context using remote login and
> password (last option in security tab).
> In this way every user on every database can use this linked server,
> this is correct (for me), but i have an exception.
> I have one user that must not be able to use linked server. How may i
> do?

> Thanks.- Nascondi testo citato

- Mostra testo citato -

But my problem is that the user is db_owner on his database. So i can
create the SP, but what block him to use a "select" in query anlyzer
or other DB connection?
He do not have to use any linked server, so there is a way to deny
SELECT on all linked server.

Thanks.

.