none
How to script user mappings

    Question

  • Hi,

    My app uses a SQL Server 2005 database. Everytime I restore a backup from my development server to a QA server, I have to manually open SSMS on the target server and edit the User Mapping for a Login used by the app, adding a role to the user for that database. 

    Before rolling this app into production, I need to script this edit. I looked at the system stored procedures, but nothing leaps out.  Any suggestions?

    Thanks.

    Dan
    Dan Hurwitz
    Tuesday, October 20, 2009 6:56 PM

Answers

  • If you are using SQL authenticated login for your application , make sure that the same login exists on all the three servers . So evertime you restore the database from other server , you many just need to run sp_change_users_login and get the report and fix them (To map the login on the destinatation server with the user on the restored db )
    Thanks, Leks
    Tuesday, October 20, 2009 7:59 PM
  • Try this

    declare @orphanuser varchar(128),@sqlstring nvarchar(1024)
    declare cur_orphan cursor
    for
    select name
      from dbo.sysusers
     where issqluser = 1 
       and (sid is not null and sid <> 0x0)
       and suser_sname(sid) is null
     order by name
    
    open cur_orphan
    
    fetch next from cur_orphan into @orphanuser
    while (@@fetch_status=0)
    begin
         set @sqlstring='exec sp_change_users_login ''auto_fix'','''+@orphanuser+''''
         --print @sqlstring
         exec sp_executesql @sqlstring
         fetch next from cur_orphan into @orphanuser
    end
    close cur_orphan
    deallocate cur_orphan

    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, October 21, 2009 2:40 AM
    Moderator

All replies

  • Hi,

    Try with the following stored procedures.

    sp_helplogins
    sp_helpuser
    sp_helpsrvrolemember
    Tuesday, October 20, 2009 7:55 PM
  • If you are using SQL authenticated login for your application , make sure that the same login exists on all the three servers . So evertime you restore the database from other server , you many just need to run sp_change_users_login and get the report and fix them (To map the login on the destinatation server with the user on the restored db )
    Thanks, Leks
    Tuesday, October 20, 2009 7:59 PM
  • Try this

    declare @orphanuser varchar(128),@sqlstring nvarchar(1024)
    declare cur_orphan cursor
    for
    select name
      from dbo.sysusers
     where issqluser = 1 
       and (sid is not null and sid <> 0x0)
       and suser_sname(sid) is null
     order by name
    
    open cur_orphan
    
    fetch next from cur_orphan into @orphanuser
    while (@@fetch_status=0)
    begin
         set @sqlstring='exec sp_change_users_login ''auto_fix'','''+@orphanuser+''''
         --print @sqlstring
         exec sp_executesql @sqlstring
         fetch next from cur_orphan into @orphanuser
    end
    close cur_orphan
    deallocate cur_orphan

    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, October 21, 2009 2:40 AM
    Moderator