Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian