Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2004

    Unanswered: connecting to named instance

    We have a script used to reconnect database users to system logins the problem is our customer needs to run this on a named instance where the target master database is not the default instance. We tried putting in the server\instance.master.dbo.sysdatabases and get an error on the '\' this sentence is in the string and '\\' did not work what am I missing? Here is the script.

    -- This script can be run using SQLQuery Analyzer
    -- It loops through every NON system database to:
    -- 1. Create Logins for Users in the restored database, but not having a SQL Server Login
    -- 2. Relinks users id in the restored database to the UIDs in the logins table
    declare @dbname varchar(32)
    declare @uname nvarchar(32)
    exec ('declare dbname_cur cursor for
    select name from master.dbo.sysdatabases
    where name not in (''MASTER'',''MODEL'',''MSDB'',''NORTHWIND'',''PUB S'',''TEMPDB'')')
    open dbname_cur
    fetch next from dbname_cur into @dbname
    while (@@fetch_status = 0)
    exec('declare indx_cur1 cursor for
    select name from ' + @dbname + '.dbo.sysusers where altuid=0 and name not in (''dbo'')')
    open indx_cur1
    fetch next from indx_cur1 into @uname
    while (@@fetch_status = 0)
    if (select name from master..syslogins where name = @uname) is null
    print '*** Add Login for User '+ @uname + ' *** '
    exec sp_addlogin @loginame = @uname , @passwd = @uname ,@defdb = @dbname

    print '*** Linking User '+ @uname + ' *** ' + ' in database '+ @dbname
    exec sp_change_users_login @Action=Update_One, @UserNamePattern = @uname , @LoginName = @uname
    fetch next from indx_cur1 into @uname
    close indx_cur1
    deallocate indx_cur1
    fetch next from dbname_cur into @dbname
    close dbname_cur
    deallocate dbname_cur

    I have attached a copy of the script if you want it you can have it once it is fixed for named instances.

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts