April 14, 2011 at 6:47 pm
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.
April 15, 2011 at 1:28 pm
I'd setup a trace to see what the GUI is trying to access.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 5:10 pm
Thanks Jack. I will try that and see.
April 17, 2011 at 2:50 pm
Hi,
Even after applying service pack SP4 am getting the below error while am trying to see the properties of a Linked server.
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
------------------------------
windows uuser is "winuser"
he has setupadmin,public server role.
and also granted,
grant execute on xp_prop_oledb_provider to [machine\winuser]
But, the setupadmin has below permissions for 'setupadmin'.
sp_srvrolepermission 'setupadmin'
/*
setupadmin Add member to setupadmin
setupadmin Add/drop/configure linked servers
setupadmin Mark a stored procedure as startup
*/
the winuser also has below server permissions
MAcHine\winuser ALTER ANY LINKED SERVER GRANT NULL
MAcHine\winuser CONNECT SQL GRANT NULL
I switch on the profiler and see what is happening for this user and i saw below select stmt. I pulled out the SELECT and executed as machine\winuser and it returned 1*4 = 4 i.e since he is part of setupadmin
select is_srvrolemember('sysadmin') * 1 +is_srvrolemember('serveradmin') * 2 +is_srvrolemember('setupadmin') * 4 +is_srvrolemember('securityadmin') * 8 +is_srvrolemember('processadmin') * 16 +is_srvrolemember('dbcreator') * 32 +is_srvrolemember('diskadmin') * 64+ is_srvrolemember('bulkadmin') * 128
--4
Nothing much i can find in the trace.
I scwitched on Errir and warings, TSQL , Security Audit, Stored procedures and Session in the profiler but nothing helped.
Do we need to give a login "sysadmin" privileges or what ???
Can anyone answer this question.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply