?
Solved

Change User Password in SQL2008

Posted on 2013-06-07
10
Medium Priority
?
365 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

762 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