Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_resolve_logins

  No additional text.


Syntax
create procedure sys.sp_resolve_logins
(
    @dest_db         sysname
   ,@dest_path       nvarchar(255)
   ,@filename        nvarchar(255)
)
as
begin
    set nocount on
    declare   @retcode int
                ,@command nvarchar(4000)
                ,@lgnname sysname
                ,@lgnsid varbinary(85)
                ,@usrname sysname

    
    -- security check
    
    exec @retcode = sys.sp_MSlogshippingsysadmincheck
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- ERROR IF IN USER TRANSACTION
    
    IF @@trancount > 0
    BEGIN
        raiserror(15289,-1,-1)
        RETURN (1)
    END
    
    -- Validate the directory the dat file is in.
    -- Remove heading and trailing spaces
    -- If the last char is '\', remove it.
    
    select @dest_path = RTRIM(LTRIM(@dest_path))
    if substring(@dest_path, len(@dest_path),1) = N'\'
        select @dest_path = substring(@dest_path, 1, len(@dest_path)-1)
    
    -- Don't do validation if it is a UNC path due to security problem.
    -- If the server is started as a service using local system account, we
    -- don't have access to the UNC path.
    
    if substring(@dest_path, 1,2) != N'\\'
    BEGIN
        select @command = N'dir "' + REPLACE(sys.fn_escapecmdshellsymbolsremovequotes(@dest_path) collate database_default, N'''', N'''''' ) + N'"'
        begin try
        exec @retcode = master.dbo.xp_cmdshell @command, N'no_output'
        end try
        begin catch
            select @retcode = 1
        end catch
        if @@error != 0
            RETURN (1)
        if @retcode != 0
        BEGIN
            raiserror (14430, 16, -1, @dest_path)
            RETURN (1)
        END
    END
    
    -- CREATE the temp table for the datafile
    -- This method ensures we are always getting the
    -- real table definition of the syslogins table.
    
    select *
    into #sysloginstemp
    from master.dbo.syslogins
    where sid = 0x00
    truncate TABLE #sysloginstemp
    
    -- BULK INSERT the file into the temp table.
    
    select @command = REPLACE(sys.fn_escapecmdshellsymbolsremovequotes(@dest_path + N'\' + @filename) collate database_default, N'''', N'''''' )
    begin try
    exec(N'BULK INSERT #sysloginstemp FROM "'
                + @command
                + N'" WITH (DATAFILETYPE = ''widenative'', KEEPNULLS)')
    end try
    begin catch
        select @retcode = 1
    end catch
    if @@error != 0 or @retcode != 0
        return 1
    
    -- UPDATE the SID in the destination database to the value in the current server's
    -- syslogins table ensuring that the names match between the source and destination
    -- syslogins tables.  Do this by cursoring through each login and executing
    -- sp_change_users_login for each login that require a SID resynch.
    
    -- DECLARE & OPEN CURSOR over old login names
    select @command = quotename(@dest_db) + N'.dbo.sp_change_users_login'

    declare #loginmapping CURSOR LOCAL FOR
        select name, sid from #sysloginstemp
    open #loginmapping
    fetch #loginmapping into @lgnname, @lgnsid
    while (@@fetch_status >= 0)
    begin
        -- GET NAME OF USER THAT NEEDS TO BE RE-MAPPED FOR THIS LOGIN
        select @usrname = NULL
        select @usrname = u.name
        from dbo.sysusers u, master.dbo.syslogins l
        where u.sid = @lgnsid
        and l.loginname = @lgnname
        and l.sid <> u.sid
        -- IF WE HAVE A USER NAME, DO THE REMAPPING
        IF @usrname IS NOT NULL
        BEGIN
            exec @retcode = @command @Action = 'Update_One'
                                        ,@UserNamePattern = @usrname
                                        ,@LoginName = @lgnname
            if @@error != 0 or @retcode != 0
                return 1
        END
        -- GET NEXT LOGIN-MAPPING
        fetch #loginmapping into @lgnname, @lgnsid
    end
    close #loginmapping
    deallocate #loginmapping
    
    -- all done
    
    RETURN  (0)
end

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash