July 8, 2009 at 3:24 pm
I have several servers. Server 1 is SS2000, Servers 2 and 3 are SS2005. I have linked 2 to 3 and 1 to 2. I have two windows logins that are in the sysadmin role on all servers. Neither login is an owner of a database. Login A can connect to server 1 in mgmt studio and see all the databases on server 2. Login B connects to server 1 in mgmt studio but can only see one database on server 2. But both can connect to server 2 and see all the databases in the linked server 3. Server 1 (the SS2000 box) has a number of windows logins mapped to the sa, and one sql login mapped to a local login on server 2. When I change the "...be made using this security context" to sa, it instead inserts the local login. Any idea what's going on here?
July 8, 2009 at 4:15 pm
Without an example its quite complicated to solve....
Try below script as it is, just change the server name "SC-SALES-RPT", after this you should be able to access other server with any common login, if NT login doesn't work then test with a new sql user having it first SA rights....and make sure you have sp2 installed...
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SC-SALES-RPT')EXEC master.dbo.sp_dropserver @server=N'SC-SALES-RPT', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SC-SALES-RPT', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SC-SALES-RPT',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'use remote collation', @optvalue=N'true'
GO
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:16 pm
Without an example its quite complicated to solve....
Try below script as it is, just change the server name "SC-SALES-RPT", after this you should be able to access other server with any common login, if NT login doesn't work then test with a new sql user having it first SA rights....and make sure you have sp2 installed...
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SC-SALES-RPT')EXEC master.dbo.sp_dropserver @server=N'SC-SALES-RPT', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SC-SALES-RPT', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SC-SALES-RPT',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SC-SALES-RPT', @optname=N'use remote collation', @optvalue=N'true'
GO
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:19 pm
Without an script used its quite complicated to solve....
Try below script as it is, just change the server name "SC-SALES-RPT", after this you should be able to access other server with any common login, if NT login doesn't work then test with a new sql user having it first SA rights....and make sure you have sp2 installed...
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SC-SALES-RPT')EXEC master.dbo.sp_dropserver @server=N'SC-SALES-RPT', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SC-SALES-RPT', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SC-SALES-RPT',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:30 pm
Without an script used its quite complicated to solve....
Try below link, just change the server name "SC-SALES-RPT", after this you should be able to access server with any common login with same password on both database servers, if NT login doesn't work then test with a new sql user giving it first SA rights....and make sure you have sp2 installed...
http://www.starinnovative.com/Sql_Scripts/CreateLinkedserver.html
Once it works for you then do the changes one by one to find out the exact cause causing it!!
Have fun!!
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:30 pm
Without an script used its quite complicated to solve....
Try below link, just change the server name "SC-SALES-RPT", after this you should be able to access server with any common login with same password on both database servers, if NT login doesn't work then test with a new sql user giving it first SA rights....and make sure you have sp2 installed...
http://www.starinnovative.com/Sql_Scripts/CreateLinkedserver.html
Once it works for you then do the changes one by one to find out the exact cause causing it!!
Have fun!!
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:31 pm
Without an script used its quite complicated to solve....
Try below link, just change the server name "SC-SALES-RPT", after this you should be able to access server with any common login with same password on both database servers, if NT login doesn't work then test with a new sql user giving it first SA rights....and make sure you have sp2 installed...
Once it works for you then do the changes one by one to find out the exact cause causing it!!
Have fun!!
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:31 pm
Without an script used its quite complicated to solve....
Try below link, just change the server name "SC-SALES-RPT", after this you should be able to access server with any common login with same password on both database servers, if NT login doesn't work then test with a new sql user giving it first SA rights....and make sure you have sp2 installed...
Once it works for you then do the changes one by one to find out the exact cause causing it!!
Have fun!!
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:32 pm
Without an script used its quite complicated to solve, Try below link
Once it works for you then do the changes one by one to find out the exact cause causing it
Have fun
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:32 pm
Without an script used its quite complicated to solve, Try below link
Once it works for you then do the changes one by one to find out the exact cause causing it
Have fun
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:32 pm
Without an script used its quite complicated to solve, Try below link
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 8, 2009 at 4:37 pm
Not sure what happened!!! I was constantly getting error message that posting failed....but it seems it was getting added every time I tried....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 9, 2009 at 4:59 pm
Thanks, but I'm trying to narrow permissions down, not grant sa permissions to everyone who uses that linked server.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply