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)

7 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
  4. It's interesting that many of the bloggers your tips helped to clarify a few things for me as well as giving.. very specific niche content. And tell people specific ways to live their lives.Sometimes you just have to yell at people and give them a good shake to get your point across.
    CRM Software in Dubai
    CRM Software Companies in Dubai

    ReplyDelete
  5. The given information was very excellent & Great tips, and awesome way to get exert tips from everyone, not only i like that post all peoples like that post, because of all given information was wonderful and it's very helpful for me.
    CRM Software In India
    CRM Software In Chennai

    ReplyDelete
  6. I will right away snatch your rss feed as I can not find your e-mail subscription link or e-newsletter service. Do you have any? Please permit me understand in order that I may just subscribe. Thanks.
    startup funding

    ReplyDelete
  7. This comment has been removed by the author.

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