locked
How to see if SIDs are consistent for DB Users and SQL Logins in 2008 RRS feed

  • Question

  • Hi, SQL noob, I have restored databases with a mix of SQL and domain Users and SQL server logins that were imported over via script http://support.microsoft.com/kb/918992/en-us.

    I'm still having trouble with my applications, and want to know how to verify that the non-domain, SQL server accounts are consistent between the SQL and the dbs -- essentially if the SIDs match up.

    Is there a way to verify via SSMS?

    THanks,

    Scott



    • Edited by scogordo Friday, September 19, 2014 7:07 PM
    Friday, September 19, 2014 7:04 PM

Answers

All replies

  • Hello,

    Let me know if the following resources are enough.

    http://msdn.microsoft.com/en-us/library/ms175475(v=sql.100).aspx

    http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

    Do you know you can transfer logins from one instance to another, if the SQL Server 2000 still exists.

    http://support.microsoft.com/kb/246133/en-us

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com



    Friday, September 19, 2014 7:08 PM
  • Run this in the database to see users which are not mapped correctly:

    SELECT UserName = name FROM sysusers 
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0) 
    and suser_sname(sid) is null 
    ORDER BY name 

    To fix them run:

    DECLARE @username varchar(25) 
    
    DECLARE fixusers CURSOR 
    FOR 
    
    SELECT UserName = name FROM sysusers 
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0) 
    and suser_sname(sid) is null 
    ORDER BY name 
    
    OPEN fixusers 
    
    FETCH NEXT FROM fixusers 
    INTO @username 
    
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    IF @username='dbo' 
    BEGIN 
    EXEC sp_changedbowner 'sa' 
    END 
    ELSE 
    BEGIN 
    EXEC sp_change_users_login 'update_one', @username, @username 
    END 
    FETCH NEXT FROM fixusers 
    INTO @username 
    END 
    
    
    CLOSE fixusers 
    DEALLOCATE fixusers 
    END 
    
    

    Friday, September 19, 2014 7:28 PM
  • Hey Tom, ran

    SELECT UserName = name FROM sysusers
    WHERE issqluser
    = 1 and (sid is not null and sid <> 0x0)
    and suser_sname(sid) is null
    ORDER BY name

    which generated no results under UserName.

    I'm in the clear?

    Friday, September 19, 2014 7:52 PM
  • That says you don't have any orphaned users.  You can also use the following to test for orphaned users.
    USE YourDatabaseNameHere
    GO
    EXEC sp_change_users_login @action='Report'
    If you still have difficulties connecting, it is something unrelated to orphaned users.
    Friday, September 19, 2014 8:12 PM
  • Then you have no orphaned users.  That is not your problem.

    What exactly is your error message?

    Friday, September 19, 2014 8:28 PM
  • There can still be users where name and sid do not match. This query reveals them:

    SELECT dp.name
    FROM   sys.database_principals dp
    WHERE  dp.type IN ('U', 'S')
      AND  dp.principal_id > 4
      AND  NOT EXISTS (SELECT *
                       FROM   sys.server_principals sp
                       WHERE  dp.name = sp.name
                         AND  dp.sid  = sp.sid)

    But as Tom says, it helps if you can describe your problem in more detail.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, September 19, 2014 9:23 PM
  • Thanks all.

    I looked at http://msdn.microsoft.com/en-us/library/ms179889.aspx and ran

    SELECT SUSER_SID('SampleLoginAcct);

    which returned a single 34 character value. If the SIDs were different between the restored database Security > Users and the Instance Security > Logins, would it have returned multiple values, or errored?

    Monday, September 22, 2014 1:44 PM
  • Thanks Erland, just ran it, no results under "name". Good sign, right?

    Monday, September 22, 2014 1:45 PM
  • From the application, I'm getting:


    Server Error in '/' Application.



    Login failed for user 'SQLTestAcct'.

    Description:
    An unhandled exception occurred during the execution of the current web
    request. Please review the stack trace for more information about the error and
    where it originated in the code.

    Exception Details:
    System.Data.SqlClient.SqlException: Login failed for user
    'SQLTestAcct'.

    Source Error:
    An unhandled exception was generated during the execution of the
    current web request. Information regarding the origin and location of the
    exception can be identified using the exception stack trace below.

    Stack Trace:
    [SqlException (0x80131904): Login failed for user 'SQLTestAcct'.]
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6256873
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1099
       System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
       System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +248
       System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +6271738
       System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6271704
       System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +354
       System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +703
       System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +54
       System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6272968
       System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +81
       System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1657
       System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +88
       System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6276407
       System.Data.SqlClient.SqlConnection.Open() +258
       APPNAME.APPNAMEWebPart.APPNAMEWebPart.IsValid() +551
       APPNAME.APPNAMEWebPart.APPNAMEWebPart.CreateChildControls() +522
       System.Web.UI.Control.EnsureChildControls() +146
       System.Web.UI.Control.PreRenderRecursiveInternal() +61
       System.Web.UI.Control.PreRenderRecursiveInternal() +224
       System.Web.UI.Control.PreRenderRecursiveInternal() +224
       System.Web.UI.Control.PreRenderRecursiveInternal() +224
       System.Web.UI.Control.PreRenderRecursiveInternal() +224
       System.Web.UI.Control.PreRenderRecursiveInternal() +224
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3394
    

    Monday, September 22, 2014 1:49 PM
  • That error is a generic error, it could mean any number of things.

    The first thing to do is try to connect using SSMS and login as that user.  If that works, it is not a SQL Server issue.  I would look at your connection string in the application.

    Monday, September 22, 2014 2:53 PM
  • AHA

    Mixed Mode authentication wasn't enabled

    Now it is and everything looks good

    Thanks!

    Monday, September 22, 2014 3:40 PM