Solved

Change User Password in SQL2008

Posted on 2013-06-07
10
356 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 76
TSQL recursive CTE challenge... 8 34
How to keep a record with the highest value 3 41
Split string into 3 separate fields 5 22
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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