flashtek1899
asked on
Change User Password in SQL2008
I am trying to change a user password in Microsoft SQL Server Management Studio for SQL2008. Thought I had the correct syntax but apparently not:
update ClientUsers set Password = 'XXX' where ID = 'user'
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'bbportalwebuser' to data type int.
I can only assume I am using wrong syntax.
Microsoft SQL Server Management Studio 10.0.4000.0
Microsoft Analysis Services Client Tools 10.0.4000.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.10.9200.16576
Microsoft .NET Framework 2.0.50727.5466
Operating System 6.1.7601
Thank you.
update ClientUsers set Password = 'XXX' where ID = 'user'
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'bbportalwebuser' to data type int.
I can only assume I am using wrong syntax.
Microsoft SQL Server Management Studio 10.0.4000.0
Microsoft Analysis Services Client Tools 10.0.4000.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.10.9200.16576
Microsoft .NET Framework 2.0.50727.5466
Operating System 6.1.7601
Thank you.
can you post the schema for the clientUsers table ? It looks like it's a casting error.
ASKER
I use SQL once every three to four years and I have no idea how to do that. Wish I did.
You should be able to right click the table and select "Script Table as" -> "CREATE To" -> "New Query Editor Window" and then copy/paste what it gives you.
ASKER
USE [BBNC]
GO
/****** Object: Table [dbo].[ClientUsers] Script Date: 06/07/2013 14:12:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClientUsers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Password] [nvarchar](255) NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[EMail] [nchar](100) NULL,
	[Active] [bit] NOT NULL,
	[LastName] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleInitial] [nvarchar](5) NULL,
	[InternalUser] [bit] NOT NULL,
	[Guid] [uniqueidentifier] NULL,
	[Deleted] [bit] NOT NULL,
	[ClientsID] [int] NULL,
	[RegistrationNumber] [nvarchar](50) NULL,
	[ReminderPhrase] [nvarchar](150) NULL,
	[BackofficeRecordType] [int] NULL,
	[DateLastLogin] [datetime] NULL,
	[NumFailedAttempts] [int] NOT NULL,
	[IsSupervisor] [bit] NULL,
	[MembershipRefreshedOn] [datetime] NULL,
	[CreateDate] [datetime] NULL,
	[IsProvisional] [bit] NULL,
	[TimeZoneID] [int] NULL,
	[UpdateDate] [datetime] NOT NULL,
	[CreateID] [int] NULL,
	[UpdateID] [int] NULL,
	[LockedOutTime] [datetime] NULL,
	[DefaultSiteID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_ClientUsers] UNIQUE NONCLUSTERED
(
	[ClientsID] ASC,
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_ClientSites] FOREIGN KEY([DefaultSiteID])
REFERENCES [dbo].[ClientSites] ([ID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_ClientSites]
GO
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_CreateID] FOREIGN KEY([CreateID])
REFERENCES [dbo].[ClientUsers] ([ID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_CreateID]
GO
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_TimeZone] FOREIGN KEY([TimeZoneID])
REFERENCES [dbo].[TimeZone] ([ID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_TimeZone]
GO
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_UpdateID] FOREIGN KEY([UpdateID])
REFERENCES [dbo].[ClientUsers] ([ID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_UpdateID]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_Users_Active] DEFAULT ((-1)) FOR [Active]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_Users_InternalUser] DEFAULT (0) FOR [InternalUser]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_Users_Guid] DEFAULT (newid()) FOR [Guid]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_Users_UserDeleted] DEFAULT (0) FOR [Deleted]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_ClientUsers_BackofficeRecordType] DEFAULT (0) FOR [BackofficeRecordType]
GO
ALTER TABLE [dbo].[ClientUsers] ADD DEFAULT (0) FOR [NumFailedAttempts]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_ClientUsers_DateAdded] DEFAULT (getutcdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[ClientUsers] ADD CONSTRAINT [DF_ClientUsers_UpdateDate] DEFAULT (getutcdate()) FOR [UpdateDate]
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
ASKER
I was going to go back and delete those posts but it won't let me.
Can you try searching for the user using their firstname/lastname to make they exist/the username is right?
select
firstname,
lastname,
username
from
clientUsers
where
firstname = N'<their firstname here>' and
lastname = N'<their lastname here>';
ASKER
Thank you for your help. The answer to my problem was not what I thought it would be.