none
user mapping tab in login properties is not responding

    Question

  • Hi,

    When i open properties of a login and click user mapping tab it gets hanged. Even after 30 mins there is no response. How to resolve this issue. I am using SQL Server 2008 R2 + CU1.

    This is happenning for 3 serevrs

    Tuesday, July 06, 2010 12:31 PM

Answers

  • yeh it is the same issue i am facing, when i click the user mapping tab it gets hanges and i have to kill SSMS.

    What is the resolution for it?

    There are 10000 logins, 4 databases  in the sevrer each db having 10000 logins. These logins i moved for sql server 2005 database. I alos tried to create a new login in 2008 , but it also have the sam issue.

    learn to use the sql commands to list and modify permissions.

    there's no fix available.

    you can file a wishlist item, if there isn't one already.

    it would be nice if (a) it worked in reasonable time, or at least (b) you could abort it without killing ssms, and/or (c) it could be smart and see that it was taking more than x seconds and offer to abort itself.

    Josh

    ps - just one database with a thousand or so logins/users is enough to make it intolerable, I can't imagine how long your 40,000 logins would take! 

    Friday, July 09, 2010 10:11 AM

All replies

  • Hi,

    Hard to point out the resolution .This might  not the issue with the sql server .. Through task manager check out what are all the process that are running on the machine and CPU usage ...
    ----------------
    Thanks,Suhas V
    Tuesday, July 06, 2010 2:18 PM
  • Is it just that one action that is hanging? Are you connected as a sysadmin?

    Can you look at sys.dm_exec_sessions and sys.dm_exec_requests while you are hanging and see if the the process is blocked... and on what?

    If you trace what happens when you click the user mapping tab, you'll see that SQL Server is trying to USE every single database, and then running a query in it.  My guess is that there is some problem with one or more of the databases is accessed.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, July 06, 2010 3:34 PM
    Moderator
  • Yeah, if you have a lot of users it does that.

    At least 2005 did.

    Josh

     

    Tuesday, July 06, 2010 7:07 PM
  • As Kalen said, check if there is blocking being experienced by the SSMS queries. Also, start a profiler trace and check how long is the query executed by SSMS taking to complete. It could be that the background queries executed by SSMS are taking a long time to complete due to large number of logins on your SQL instance. You would want to capture the following events in SQL Profiler:

    RPC: Completed

    RPC: Starting

    SP: Starting

    SP: Completed

    SP: StmtCompleted

    SP: StmtStarting

    TSQL: Batch Completed

    TSQL: Batch Starting

    TSQL: Stmt Starting

    TSQL: Stmt Completed

    If the number of events are too high, then you can add filters for Application Name to reduce the number of events captured by the SQL Profiler. If you intend to use a client side profiler, then please log into the SQL box and the run the SQL profiler from the console. Remote profiler traces can hurt SQL performance for high end OLTP servers. Or you could capture a server side profiler trace.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Tuesday, July 06, 2010 9:35 PM
  • But Amit, as I said, it "just does" that, if you have lots of users, I believe, and/or lots of permissions, I never did track it all the way down.  There's no solution that I know of, except for Microsoft to fix some really bad code.  OP should just use system SPs and commands instead of the badly designed widget.

    Josh

     

    Tuesday, July 06, 2010 11:26 PM
  • This is currently a hunch for both of us. We need to drill down and identify if that is the exact issue and there is no blocking involved. If that is the case, then a Connect Item can be filed.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Wednesday, July 07, 2010 3:03 AM
  • apologies for late response.

    when the usr mapping is hanged I have noticed in the SQL Server activity monitor there is a wait type "preemptive_OS_lookupaccountsid"

    what is the root cause for this wait type and how do i resolve this?

    Thursday, July 08, 2010 1:40 PM
  • I connected as windows admin which is sys admin in sql server. This server is not yet into production, i am the only user connected to this server. I ran sp_who2 and there are no blockings.

     

     

    Thursday, July 08, 2010 2:03 PM
  • The waittype means that we are waiting for the function "LookupAccountSID" to complete (http://msdn.microsoft.com/en-us/library/aa379166(VS.85).aspx). If the waittime for this particular waittype is not increasing, then the wait can be ignored.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Thursday, July 08, 2010 2:13 PM
  • This is currently a hunch for both of us. We need to drill down and identify if that is the exact issue and there is no blocking involved. If that is the case, then a Connect Item can be filed.

    OP has not stated how many logins, users, or permission items in his database.

    But I did drill down on this a year or so ago, fired up profiler, enabled trace of SSMS, and watched all the pretty queries going by.  Ugh.  Had to kill SSMS a dozen times when I forgot and clicked the tab.  So I know the behavoir of SQL Server.  Of course I am not certain that's what OP has going on, but it sure sounds like it.

    Josh

    Thursday, July 08, 2010 9:13 PM
  • yeh it is the same issue i am facing, when i click the user mapping tab it gets hanges and i have to kill SSMS.

    What is the resolution for it?

    There are 10000 logins, 4 databases  in the sevrer each db having 10000 logins. These logins i moved for sql server 2005 database. I alos tried to create a new login in 2008 , but it also have the sam issue.

    Friday, July 09, 2010 5:34 AM
  • yeh it is the same issue i am facing, when i click the user mapping tab it gets hanges and i have to kill SSMS.

    What is the resolution for it?

    There are 10000 logins, 4 databases  in the sevrer each db having 10000 logins. These logins i moved for sql server 2005 database. I alos tried to create a new login in 2008 , but it also have the sam issue.

    learn to use the sql commands to list and modify permissions.

    there's no fix available.

    you can file a wishlist item, if there isn't one already.

    it would be nice if (a) it worked in reasonable time, or at least (b) you could abort it without killing ssms, and/or (c) it could be smart and see that it was taking more than x seconds and offer to abort itself.

    Josh

    ps - just one database with a thousand or so logins/users is enough to make it intolerable, I can't imagine how long your 40,000 logins would take! 

    Friday, July 09, 2010 10:11 AM
  • Is this a bug in SQL Server 2008 R2 or can it be fixed by making any changes, because same number of logins are working fine with SQL Servr 2005 with out any problem.
    Friday, July 16, 2010 10:22 AM
  • Even I am facing the same kind of issue when no of users got increased per database, Is any update / fix available for it???

     

    waiting for reply


    Prashant Deshpande
    Wednesday, August 10, 2011 7:36 AM