Tuesday, March 20, 2012

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)

3 comments:

  1. Great article, thank you very much for sharing!

    ReplyDelete
  2. Wonderful!! Exactly what I was looking for. Thank you for this post!!

    ReplyDelete
  3. Many thanks for the information.

    ReplyDelete

MS CRM 2011 KB Article customization Issue.

Recently I have encountered some issue while customizing Kb Article Entity. I was doing following configuration in Article form. 1. Add Ba...