Login 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)

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 16:04:54

I have a stored procedure which is running under the Database, as of yesterday it was ok to do so, but it started complaining about the USER profile is complaining since the Error reported as LOGIN FAILED FOR THE USER 'PDST'

Message 18546 Then I have re-created the USER PDST with certain roles and still it says the SP with login failed as claimed above

I have tried to run the body of the SP but still complains.

Tried all the ways but have no clue for that.

Can anyone please help me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 16:05:55

You'll need to post it in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 16:30:21

Under the Database there is a SP, which was running fine.

But all of sudden it started complaining as

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'PDST'.

I know you might be telling there is no such user called PDST but there is no such user under the DATABASE USER's (Till Other day) but under the Main SECURITY Tab of the Server's Instance the user 'PDST' is existing.

Now What I couldn't understand is this User PDST needs to be created under the Database Tab of USER?

Is it require the Stored Procedure to run under the USER (PDST)nemesis?

Or Do I need to recreate the Stored Procedure but what is the guarantee still it won't complain of the User PDST?

Please help me many thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 16:50:47

So where's the code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 17:12:42

CODE:

USE [Orglrel]
GO
/****** Object: StoredProcedure [dbo].[sp_CreateListofExports] Script Date: 01/21/2011 07:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description: Used for the ListofExports. Goes through the list of databases and
-- extracts a list of appointments for the next appointment date.
--
---- Removed the dtCancelled from the where clause
-- =============================================
CREATE PROCEDURE [dbo].[sp_CreateListofExports]

AS
DECLARE @dNextAppointmentDate1 nvarchar(50),
@dNextAppointmentDate2 nvarchar(50),
@cSQL nvarchar(2000)--,
--@ServerName NVARCHAR(50)
BEGIN
--Date: 09/02/2009
--Checks to see if it is a public holiday as it is scheduled to run only Monday to Friday
--Only runs the process when it isn't a public holiday
--Made this change so that when it runs automatically, the CSV file is only created on a
-- day that isn't a public holiday or weekend.
if (Select Convert(Int, Is_PublicHoliday)
From dbo.tblNextAppointmentDateLookup
Where Convert(Varchar,LookUpdate,112) = Convert(Varchar,getdate(),112)) = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Set the waitlist date and calculate the number of days in the waitlist date's month
SET @dNextAppointmentDate1 = (Select top 1 CONVERT(varchar,LookupDate,121) AS LookupDate1
From tblNextAppointmentDateLookup
Where Lookupdate > GetDate() and Is_WeekEnd = 0 -- Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LookupDate)

SET @dNextAppointmentDate2 = (Select Case When Upper(DateName(weekday, GetDate())) = 'FRIDAY' Then
(Select top 1 CONVERT(varchar,LookupDate,121)
From (Select top 2 CONVERT(varchar,LookupDate,121) AS LookupDate
From tblNextAppointmentDateLookup
Where Lookupdate > GetDate() and Is_WeekEnd = 0 and Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LookupDate) As NewAppointmentDateLookup
Order by LookupDate Desc)
Else
(Select top 1 CONVERT(varchar,LookupDate,121)
From (Select top 2 CONVERT(varchar,LookupDate,121) AS LookupDate
From tblNextAppointmentDateLookup
Where Lookupdate > GetDate() and Is_WeekEnd = 0 and Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LookupDate) As NewAppointmentDateLookup
Order by LookupDate) End As LookupDate2)

--Adds the server name
--SET @ServerName = '[SQLSERVER02\SQL02]'

DECLARE @CurrentDatabaseName SysName,
@ServerName Varchar(50)

-- Declare cursor - Select only the Databases marked as Active
DECLARE DatabaseName_Cursor CURSOR FOR
SELECT DatabaseName, ServerName
FROM tblDatabaseList
WHERE Active = 1 And ExportCSVFileOnly = 0 --only pickup the ones that don't have a CSV file export
ORDER BY DatabaseName

-- open cursor
OPEN DatabaseName_Cursor

--No longer deleting all the records - Will keep for reporting
--Delete all records in the Daily Appointment Table
--TRUNCATE TABLE tblDailyAppointmentReminderList

FETCH NEXT FROM DatabaseName_Cursor INTO @CurrentDatabaseName, @ServerName
--Go through the list of databases and get the next days appointments
-- Note: It will only pickup appointment with wStatus = 1 or 6 = Booked or Confirmed
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @cSQL = 'INSERT INTO [listexports].dbo.tblDailyAppointmentReminderList(ServerName, DatabaseName, RoomName, ClinicSiteDescription, ClinicSitePhone,URNo, Firstname, MobilePhone, AppointmentDate, AppointmentTime, CreatedDate)
SELECT DISTINCT ''' + @ServerName + ''',''' + @CurrentDatabaseName + ''', ABBS9.Room, CRD.ClinicSiteDescription, CRD.ClinicSitePhone, Code, Replace(LIST4.firstName,''"'','''') As [Firstname],
(Case When Left(LIST4.phoneOther,2)= ''04'' Then LIST4.phoneOther
When Left(LIST4.homePhone,2)= ''04'' Then LIST4.homePhone
When Left(LIST4.workPhone,2)= ''04'' Then LIST4.workPhone
Else LIST4.phoneOther End )As MobileNumber,
CONVERT(VARCHAR,ABBS9.dtDate,121) As [AppointmentDate],
CONVERT(VARCHAR, DateAdd(Minute, -(IsNull(MinutesB4Appt,0)), ABBS9.tmTime) ,108) As [AppointmentTime],
GetDate()
FROM ' + @ServerName + '.' + @CurrentDatabaseName + '.dbo.ABBS9 As ABBS9
INNER JOIN ' + @ServerName + '.' + @CurrentDatabaseName + '.dbo.LIST4 As LIST4 ON ABBS9.ridPatient = LIST4.RecordNum
INNER JOIN [SQLSERVER02\SQL02].[listexports].dbo.tblRoomClinicContactDetails As CRD ON CRD.DatabaseName = ''' + @CurrentDatabaseName + ''' And ABBS9.Room = CRD.RoomName
WHERE (ABBS9.dtDate Between ''' + @dNextAppointmentDate1 + ''' AND ''' + @dNextAppointmentDate2 + ''') AND ABBS9.wStatus IN (1,6)'
-- Angelo D'Acri - 15/02/2010 - Removed 'AND ABBS9.dtCancelled IS NULL' from Where Clause
EXEC (@cSQL)
END
FETCH NEXT FROM DatabaseName_Cursor INTO @CurrentDatabaseName, @ServerName
END

CLOSE DatabaseName_Cursor
DEALLOCATE DatabaseName_Cursor

--Sets the phone to the default Telephone number if no number found
SET @cSQL = 'UPDATE DAR
SET DAR.ClinicSiteDescription = CRD.ClinicSiteDescription, DAR.ClinicSitePhone = CRD.ClinicSitePhone,
AppointmentTime = CONVERT(VARCHAR, DateAdd(Minute, -(MinutesB4Appt), AppointmentTime) ,108)
FROM [listexports].dbo.tblDailyAppointmentReminderList DAR INNER JOIN [listexports].dbo.tblRoomClinicContactDetails CRD
ON DAR.DatabaseName = CRD.DatabaseName And CRD.RoomName = ''Default''
WHERE DAR.ClinicSitePhone IS Null And DAR.ClinicSiteDescription IS Null'
EXEC (@cSQL)
END -- end that checks if not a public holiday
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 17:15:30

I think you are getting the error because of the linked server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 17:23:15

Tkizer Many thanks.

As you say Link server means where I need to ensure,

Yep there is link server as this database resides in the Clustered Server and I have queried to get the Link Server databases queried perfectly.

Can you be specific where I can look into as to how I need to trouble shoot this.

Once again thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 17:25:18

We'll need you to be more specific actually. When are you getting that error? When you execute the stored procedure or when you log into Management Studio or the application? We need specifics.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 17:32:00

This Particular SP is run through the SQL Agent Service and SAME Error.

While Executing on SSMS and executing as SP same error.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 17:36:54

It's the linked server then. Verify the config and correct it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 17:55:36

Sorry I have verified the Linked Server is working to and fro.

Any solution please.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 18:16:02

Hi Tkizer,

I have 3 Clustered servers

Between 1 and 2 Link Server is working

Between 2 and 1 Link Server is working.

Now Cluster Server 2 is not accessing the Data from Server 3

(I have Configured now with Linked server on Server 3 and vice-versa)

Complaining the DHSV Login

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'PDST'.

What should be the solution.

many thanks I think you are right TKIZER.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 18:49:25

I know I'm right.

You'll need to fix the linked server config. What settings you use is up to you not me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-20 : 23:31:30

The Settings configuredfine for us but the application is the cause of concern and culprit and made several inroads for me to look into, however, you have been great source

.

Thanks my best Technical Mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources