Solved

Change User Password in SQL2008

Posted on 2013-06-07
10
350 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
  • 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now