deny select on linked server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Selvad71 - 25 Nov 2008 08:39 GMT

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.

Reply to this Message

Uri Dimant - 25 Nov 2008 10:29 GMT

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

> Hi,
> i've a linked server. i force security context using remote login and
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

Selvad71 - 25 Nov 2008 11:59 GMT

> Create a stored procedure which executes Linked server query and DENY
> EXECUTE permission to this user.
[quoted text clipped - 11 lines]
>
> - 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.

Reply to this Message

Russell Fields - 25 Nov 2008 18:12 GMT

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

>> Create a stored procedure which executes Linked server query and DENY
>> EXECUTE permission to this user.
[quoted text clipped - 19 lines]
>
> Thanks.

Reply to this Message

Uri Dimant - 26 Nov 2008 06:23 GMT

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

>> Create a stored procedure which executes Linked server query and DENY
>> EXECUTE permission to this user.
[quoted text clipped - 19 lines]
>
> Thanks.

Reply to this Message