CRM 2011 - Update user time zone from SQL query

Recently I wanted to find out users who are not in the correct time zone in CRM System. To change the time zone or user related settings we need to log in to the system by using that particular user's login details (see the below screen shot 1 and 2).

But if you system is up and running it is not possible to ask there login details. so here it the solution. login to the system as System admin, then go to the File , Option and "General" Tab Set the time zone and click "OK".

In CRM we have separate table "[UserSettingsBase] " , which contains all the user related setting information. for an example time zone, currency, language and ..... Use the following script to get the admin users time zone details from the database.

select TimeZoneBias, TimeZoneCode from [UserSettingsBase]
where SystemUserId In (select distinct SystemUserId from SystemUserBase where DomainName like '%DOMAIN\USERNAME%')

Find out which users are not in the correct time zone. use following script.

select FullName , CreatedByName, CreatedOn, DomainName, ModifiedByName from SystemUser
where SystemUserId In (
SELECT  SystemUserId
FROM [UserSettingsBase]
where [TimeZoneBias] != -480
and [TimeZoneCode] != 215)
and IsDisabled = 0

If you want to update these users time zone with correct time zone you can simpy use following script. Don't forget to give an iisreset to view your changes.

update [UserSettingsBase]
set [TimeZoneBias] = -330, TimeZoneCode = 200
where SystemUserId IN (select SystemUserId from SystemUser
  where SystemUserId In (select  SystemUserId
      from [PRM_MSCRM].[dbo].[UserSettingsBase]
      where [TimeZoneBias] != -480
         and [TimeZoneCode] != 215)
             and IsDisabled = 0)