deny select on linked server

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" wrote in message

> 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.
 >> Stay informed about: 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" wrote in message

> On 25 Nov, 11:29, "Uri Dimant" 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" wrote in message
>>
>>
>>
>>
>>
>> > 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.
 >> Stay informed about: deny select on linked server 

Hi
I cannot test it right now, but take a look at EXECUTE AS (you can use it
within SP)clause or CREATE CERTIFICATE commands in the BOL.

Erland wrote a great article for the subject
http://www.sommarskog.se/grantperm.html






"Selvad71" wrote in message

> On 25 Nov, 11:29, "Uri Dimant" 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" wrote in message
>>
>>
>>
>>
>>
>> > 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.
 >> Stay informed about: deny select on linked server