RE: security/login problems

From: Gary Whitley [MSFT] (garyw_at_online.microsoft.com)
Date: 12/01/03

  • Next message: Kevin McDonnell [MSFT]: "RE: xp_cmdshell - Error 997 from GetProxyAccount on line 604" 
    Date: Mon, 01 Dec 2003 22:39:32 GMT

    Hi Alex,

    I may not have enough information to give you a direct answer, but I
    believe I can get started in the right direction. The symptoms you
    describe can occur when a database is restored from a backup to a different
    server from the one on which the backup was created. The same effect will
    be observed if you detach a database and attach it to another server.

    The issue in these cases involves the security identifiers for the logins
    of the server compared to the user security identifiers stored in the
    database. The security identifiers for the logins on the new server are
    different from the security identifiers on the original server. Therefore,
    they do not match the user security identifiers stored in the database.

    The resolution in these cases is to use sp_dropuser to drop the user IDs
    from the database and then add them back on the new server. This process
    recreates the security identifiers for the users in the database and they
    will match the SIDs for the logins on the new server.

    The only problem with this technique is when objects in the database are
    owned by users other than DBO. If a user owns objects in the DB and you
    attempt to drop the user, the action will fail. You will have to change
    the object ownership to another user ID, probably DBO, in order to be able
    to drop the affected user. This is not a problem unless you have a large
    number of objects owned by individual users in the database.

    Assuming the Pubs and/or Northwind database exists on the server, you can
    test the creation of new logins with these databases. If you have deleted
    these databases, you may simply create a new database and test logins with
    the new database.

    This issue and the steps to resolve the issue are discussed in Knowledge
    Base article 168001.

    Thanks.

    Gary Whitley

    This posting is provided "AS IS" with no warranties, and confers no rights.

  • Next message: Kevin McDonnell [MSFT]: "RE: xp_cmdshell - Error 997 from GetProxyAccount on line 604"

    Relevant Pages

    • RE: Creating a DB User *not* on current Machine
      ... detach/attach the database to a new server. ... Server.When you move a database from one server that is running SQL Server ... the security identification numbers of the logins in the master ...
      (microsoft.public.sqlserver.security)
    • Create SharePoint Portal failed.
      ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
      (microsoft.public.sharepoint.portalserver)
    • Re: Logins
      ... no databases are checked, ... On the Server Roles tab, ... That server was upgraded to SQL Server ... moved, I recreated the SQL Server logins we had added, making sure that the ...
      (microsoft.public.sqlserver.security)
    • Re: transfering logins
      ... has their name, password, and a SID, among other attributes. ... In <your database>.dbo.sysusers, you will notice that each database user has ... database not a server object), but since the SIDs are generated at random, ... do not have this problem with Windows logins (hint: ...
      (microsoft.public.sqlserver.security)
    • Re: ADO Connection Timeout
      ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
      (microsoft.public.data.ado)
    • Quantcast