Solved

Change User Password in SQL2008

Posted on 2013-06-07
10
358 Views
Last Modified: 2013-06-12
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.
0
Comment
Question by:flashtek1899
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39229693
can you post the schema for the clientUsers table ?  It looks like it's a casting error.
0
 

Author Comment

by:flashtek1899
ID: 39229931
I use SQL once every three to four years and I have no idea how to do that.  Wish I did.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39229979
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:flashtek1899
ID: 39230147
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

0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39230167
there we go!  Try this:

use BBNC;
go

update ClientUsers set Password = N'XXX' where UserName = N'user';

Open in new window


edit:

you may also want to go and remove the post with the database creation script.  never want to leave more information about your architecture than necessary =)
0
 

Author Comment

by:flashtek1899
ID: 39230185
(0 row(s) affected)

(0 row(s) affected)
0
 

Author Comment

by:flashtek1899
ID: 39230201
I was going to go back and delete those posts but it won't let me.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39230219
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

0
 

Author Comment

by:flashtek1899
ID: 39241400
Thank you for your help.  The answer to my problem was not what I thought it would be.
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question