Microsoft® SQL Server® 2012 Bible

373

Chapter 15: Executing Distributed Queries

15

context. If the @logicallogin is null, the setting applies to all nonmapped users. The

@useself option is the same as the Impersonate option within the New Linked Server

dialog in SQL Server Management Studio.

The @useself parameter expects a true/false/null value because this parameter deter-

mines whether to connect to the remote server by impersonating local logins or explicitly

submitting a login and password. The default value is true. A value of true specifi es that

logins use their own credentials to connect to the remote server. The @rmtuser and

@rmtpassword arguments will be ignored when the statement is executed. A value of

false specifi es that the @rmtuser and @rmtpassword arguments connect to the remote

server specifi ed for the @locallogin.

The following example calls the sp_addlinkedsrvlogin system stored procedure to

enable the AvalonDev\Tester login to access the AvalonDevserver as the sa user with

the password P@$$w0rd.

EXEC sp_addlinkedsrvlogin

@rmtsrvname='AvalonDev',

@useself='FALSE',

@locallogin='AvalonDev\Scott',

@rmtuser='sa',

@rmtpassword='P@$$w0rd'

The following example sets all nonmapped users to connect using their own security con-

text. (This is the recommended option.) The local user is null, so this linked server login

applies to all nonmapped users. The @useself option is not specifi ed, so the default set-

ting, true, applies here. This causes the users to use the local security context. This is the

default setting, so you need this code if you want to return to the default setting:

EXEC sp_addlinkedsrvlogin

@rmtsrvname='AvalonDev'

This next example prevents all nonmapped users from executing distributed queries. The

second parameter, @useself, is set to false and the corresponding user login and pass-

word are left as null:

EXEC sp_addlinkedsrvlogin

@rmtsrvname='AvalonDev', @useself='false'

If you want to remove a mapped login, simply use the sp_droplinkedsrvlogin system

stored procedure:

EXEC sp_droplinkedsrvlogin

@rmtsrvname='AvalonDev', @locallogin='AvalonDev\Scott'

To remove all nonmapped users’ default mappings, run the same drop stored procedure, but

this time specify null as the local login:

EXEC sp_droplinkedsrvlogin

@rmtsrvname='AvalonDev', @locallogin=NULL

c15.indd 373c15.indd 373 7/30/2012 4:50:39 PM7/30/2012 4:50:39 PM

http://www.it-ebooks.info