SQL Server Performance

sysusers Vs syslogins

Discussion in 'SQL Server 2005 General DBA Questions' started by priyaram, Aug 1, 2007.

  1. priyaram New Member

    hi in my production servers , the sysuers and syslogins tables are not in sink, how can i make these look to be in sink,
  2. MohammedU New Member

    Check sp_change_users_login procedure in BOL....
  3. priyaram New Member

    thanks mohammed for your help.
    one more question, in a server if an user is found in test..sysusers table and not in master..syslogins tables.Is that possible.
    if possible means , with out having entry in syslogins table, how can that user access the database??
  4. satya Moderator

    The information you are looking is nothing but mismatch of sync. between syslogins and sysusers, using that SP should fix it.
    Also depends on what authentication is used. In SQL 2005 refer to sys.server_principals in this case.
  5. TRACEYSQL New Member

    i have just brought over 2000 SQL Db to SQL 2005 Db.
    If i was doing a backup and restore in sql 2000 from one sql server to another i would run this commanddeclare
    @usrname varchar(100), @command varchar(100)declare Crs insensitive cursor for
    select
    name as UserName from sysuserswhere issqluser = 1 and (sid is not null and sid <> 0x0)
    and
    suser_sname(sid) is nullorder by name
    for
    read onlyopen
    Crsfetch
    next from Crs into @usrnamewhile
    @@fetch_status=0begin
    select
    @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' 'exec
    (@command)fetch
    next from Crs into @usrnameend
    close
    Crsdeallocate
    Crs
    Now in SQL 2005 how would i fix the logins i tried to run above but i get errorsMsg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
    An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
    Thanks
  6. MohammedU New Member

    Did you move the logins?
    If not try the following...
    How to transfer logins and passwords between instances of SQL Server
    http://support.microsoft.com/kb/246133
    Note:
    When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.

Share This Page