Link to home
Start Free TrialLog in
Avatar of flashtek1899
flashtek1899Flag for United States of America

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.
Avatar of didnthaveaname
didnthaveaname
can you post the schema for the clientUsers table ?  It looks like it's a casting error.
Avatar of flashtek1899
flashtek1899
Flag of United States of America image

ASKER

I use SQL once every three to four years and I have no idea how to do that.  Wish I did.
Avatar of didnthaveaname
didnthaveaname
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.
Avatar of flashtek1899
flashtek1899
Flag of United States of America image

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of didnthaveaname
didnthaveaname
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of flashtek1899
flashtek1899
Flag of United States of America image

ASKER

(0 row(s) affected)

(0 row(s) affected)
Avatar of flashtek1899
flashtek1899
Flag of United States of America image

ASKER

I was going to go back and delete those posts but it won't let me.
Avatar of didnthaveaname
didnthaveaname
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>';

Open in new window

Avatar of flashtek1899
flashtek1899
Flag of United States of America image

ASKER

Thank you for your help.  The answer to my problem was not what I thought it would be.