LinkedSerer issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

LinkedSerer issue

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-04-14 : 20:44:10

Hi All,

Need some help regarding Linked Server.

Environment
SQL Server 2005 , sp2

Scenario
---------
Goal: I want to check the properties of my Linked server.

setup
------
- i have db machine and two instance are running SQL01 and SQL02
- From SQL01, i have a created a Linked server to SQL02 using sysadmin accnt

/* script */
/****** Object: LinkedServer [machine\SQL02] ******/

EXEC master.dbo.sp_addlinkedserver
@server = N'machine\SQL02',
@srvproduct=N'SQL Server'

/* For security reasons the linked server remote logins password is changed with */

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'machine\SQL02',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'collation compatible',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'data access',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'dist',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'pub',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'rpc',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'rpc out',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'sub',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'connect timeout',
@optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'collation name',
@optvalue=null
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'lazy schema validation',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'query timeout',
@optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption
@server=N'machine\SQL02',
@optname=N'use remote collation',
@optvalue=N'true'

- created a dummy normal windows user <machinename\winuser> on t
- Created a login <machinename\winuser> under machine\sql01 instance where the linked server has been created which is in turn pointing to source as machine\sql02.

- granted setupadmin role and ALTER ANY LINKED SERVER

EXEC master..sp_addsrvrolemember
@loginame = [machine\winuser],
@rolename = N'setupadmin'
GO

GRANT ALTER ANY LINKED SERVER to [machine\winuser];
go

Now open the management studio as user "machine\winuser"
and when i try to see the properties of the linked server which has been created it gives me the below error.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)
------------------------------
A required operation could not be completed. You must be a member of the sysadmin role to perform this operation. (SqlManagerUI)
------------------------------
BUTTONS:
OK
------------------------------

Why is it asking for "sysadmin" rights? Does the setupadmin and ALTER ANY LINKED SERVER permission is not enough or what???

I tried checking the permissions associated with "setupadmin" which are basically to add/remove/change linked servers but why am not able to do that????

sp_srvrolepermission 'setupadmin'
/*
ServerRole Permission
setupadmin Add mepppmber to setupadmin
setupadmin Add/drop/configure linked servers
setupadmin Mark a stored procedure as startup
*/

Any thoughts would be greatly appreciated.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources