x

Updating a User Password in all databases in SQL Server 2008

Hi All,

Is there are way to Update a User password in all database of SQL Server 2008 Instance?

DECLARE @user varchar(1000)

SELECT @createwebuser = 
'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? 

IF  EXISTS (UPDATE sys.database_principals set password =''sasa'' WHERE name = N''myuser'')

END'

EXEC sp_msforeachdb @createwebuser

This script is throwing up error because there is no column with name password in the sys.database_principals. Any ideas where the password of a user will store and how we can update that?

more ▼

asked Jan 28, 2010 at 10:20 PM in Default

Akbar gravatar image

Akbar
55 3 3 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Users don't have passwords, logins do. So you need only change the password once for the instance, and all databases within the instance will be 'affected'.

And... the error isn't just because the column isn't there - it's also because that's not how to change a password. Try ALTER LOGIN instead.

more ▼

answered Jan 28, 2010 at 10:23 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x31
x15

asked: Jan 28, 2010 at 10:20 PM

Seen: 1497 times

Last Updated: Jan 28, 2010 at 10:24 PM