How to get currently authenticated user in MSCRM SQL queries?

Posted on February 13, 2007 by Ayaz Ahmad [MVP - MSCRM]

During developing SQL queries for MSCRM, it’s often required to fetch the currently authenticated user.  

SUSER_SNAME() is a special function in SQL that returns the currently authenticated user. If your code doesn’t authenticate to SQL Server by using Windows authentication, your queries will never return any data, because all of the filtered views perform an inner join using systemuserid.  Here is the query to fetch currently authenticated user in SQL server. select SystemUserId, FullName from SystemUserBase

 where SystemUserBase.DomainName = SUSER_SNAME()

pic1.JPG

Filed under: CRM Development | Tagged: ,

« »

5 Responses

  1. steve, on April 22nd, 2008 at 10:39 am Said:

    I try this but this returns DOMAIN\admin on all users.(because of connecting sql with sa authentication I think)is there any other way to get UserId that currently logged on in Reporting Services?

  2. Prabhakar, on May 20th, 2008 at 7:36 pm Said:

    Steve,
    your connection to SQL must have used the domain\admin login. Try this instead:
    select systemuserid, domainname, fullname from SystemUserBase where SystemUserBase.DomainName = @uid
    In your report, set the default value of the @uid param to =User!UserID
    The report will set the value of the @uid param to the currently logged on user at run time, and run the aforementioned query. For instance, when you run the report, the query will be:
    select systemuserid, domainname, fullname from SystemUserBase where SystemUserBase.DomainName = ‘domain\steve’
    and so on.

    HTH
    -Prabhakar

  3. brad, on July 9th, 2008 at 4:10 pm Said:

    Ayaz,

    Follow Up Question for you? I love your blog, not to butter you up but I get it as an RSS feed via my PC and Cell Phone. I stumled across this article and hope you can help me as well. I have a report that is being used a dashboard. The report needs to default to the current user however when I attempt to use both methods you outlined above it appears to work just fine in VS and Report Manager (sometimes) but fails on the Reportserver. I get the following error message. “An internal error occurred on the report server. See the error log for more details. (rsInternalError)”. When I use the suggestion you have above in the comments I get the resuly of = ‘domain\steve’? Thanks.

  4. Ayaz Ahmad [MVP - MSCRM], on July 14th, 2008 at 4:25 am Said:

    Hi,

    Use following query in your report to fetch currently logged in user:

    SELECT systemuser.fullname AS ‘fullname’, usersettings.calendartype as ‘calendartype’, usersettings.uilanguageid AS ‘uilanguageid’ FROM FilteredSystemUser AS systemuser INNER JOIN FilteredUserSettings as usersettings ON systemuser.systemuserid = usersettings.systemuserid WHERE systemuser.systemuserid = dbo.fn_FindUserGuid()

    Hope this helps,

    Ayaz

  5. Abhishek, on September 15th, 2008 at 7:38 am Said:

    Hi Ayaz ,
    While installing CRM 3.0 with existing database i am getting this error.
    Authentication Error
    Microsoft CRM could not log you on to the system. Make sure your user record is enabled and that you have been assigned at least one security role. For more information, contact your system administrator.
    I tried this query -
    select SystemUserId, FullName from SystemUserBase

    where SystemUserBase.DomainName = SUSER_SNAME()
    I am not getting any data is there any other way to proceed.

Leave a Reply