Using sp_addlinkedsrvlogin - 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 2000 Forums

SQL Server Development (2000)

Using sp_addlinkedsrvlogin

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-24 : 07:10:03

I am trying to create an Audit database to keep track of all of databases that are created on the database servers. The idea is to have sprocs run each night to check the servers to see if any new databases exist, if so, they are added to the Audit database.

I need to perform distributed queries, hence, to enable server to server communication, I need to set them up as linked servers. If a server is encountered that is not set up as a linked server, then it will be. However, I keep getting the error message:

Server: Msg 18456, Level 14, State 1, Line 2
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I have done some reading on sp_addlinkedsrvlogin, but don't quite understand when and where I have to set up the login. My login has administrative rights, but I still get the above error message if I sign in as sa, rebeccaS, etc. Could you have a look at the query to tell me where I have to apply the login or just explain what I am doing wrong. Thanks

Here is the code in a very rough form:

DECLARE @lastServerID SMALLINT
DECLARE @count SMALLINT
DECLARE @numOfServers SMALLINT
SET @count = 0
SET @lastServerID = 0
		
SELECT @numOfServers = COUNT(*) FROM Server

WHILE @count <= @numOfServers
	BEGIN
		DECLARE @serverName VARCHAR(20)
		SET @serverName = (SELECT TOP 1 serverName FROM Server WHERE ServerID > @lastServerID)
		SET @lastServerID = (SELECT ServerID FROM Server WHERE ServerName = @serverName)

		--Check to see if a new database has been created on the specific server
		DECLARE @sql VARCHAR(2000)
		SET @sql = (
		'IF EXISTS(SELECT DBID FROM ' + @serverName + '.Master.dbo.sysdatabases 
			WHERE [name] NOT IN (''Pubs'',''Northwind'')
			AND DBID NOT IN (SELECT SysDatabaseID FROM FEDSDEV01.DatabaseAudit.dbo.DatabaseDetails))

			BEGIN	
				EXEC dbo.usp_InsertDatabaseDetails ' + @serverName +
			' END') 
		EXEC (@sql)	

		IF @@ERROR <> 0
			BEGIN
				IF @@ERROR = 7202
					BEGIN
						--Add linked server for the server that is not registered
						EXEC sp_addlinkedserver 
						@serverName
						, N'SQL Server'
						
						CONTINUE
					END
				ELSE 
					BEGIN
						--To be updated
						PRINT 'Error Occurred'
					END
			END	
		SET @count = @count + 1
	END

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources