Home > CRM Development > How to get currently authenticated user in MSCRM SQL queries?

How to get currently authenticated user in MSCRM SQL queries?


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

Advertisements
  1. steve
    April 22, 2008 at 10:39 am

    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
    May 20, 2008 at 7:36 pm

    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
    July 9, 2008 at 4:10 pm

    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. July 14, 2008 at 4:25 am

    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
    September 15, 2008 at 7:38 am

    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.

  6. crsb
    February 26, 2010 at 10:59 am

    Nice work! The query using the userguid function was the one that did the trick for me and saved me a lot of time!
    Thanks a lot !!

  1. February 13, 2010 at 3:57 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: