Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepltrigcertgrant

  No additional text.


Syntax

-- Name: sp_MSrepltrigcertgrant

-- Description: This proc is invoked by sp_link_publication on the
-- publisher/master db(for remote publisher) and this proc executes commands
-- to create the authentication grants for immediate updating trigger execution
-- to work under impersonation

-- Parameters: See the procedure definition

-- Returns:  0 - succeeded
--           1 - failed

-- Security: Internal. no check
-- Note: This SP uses the new EXECUTE AS functionality to execute under sa context
-- as it is created under SA account
-- Note: This SP uses xp_cmdshell
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSrepltrigcertgrant
(
    @mode tinyint    -- 1 = create/refresh, 2 = drop
    ,@islocalpub bit  -- local or remote publisher
    ,@certname sysname -- certificate name
    ,@targetdb sysname -- target database where authentication is needed
)
with execute as 'dbo'
as
begin
    set nocount on
    declare @retcode int
                ,@cmd nvarchar(4000)
                ,@login sysname
                ,@user sysname
                ,@dbname sysname
                ,@mssql_data_path nvarchar(255)
    
    -- validate @mode
    
    if (@mode not in (1,2))
        return 1
    
    -- for remote publisher - the target database is master always
    
    if (@islocalpub=0)
        select @targetdb = N'master'
    
    -- validate that we are in the right db
    
    select @dbname = db_name()
    if (@dbname != @targetdb)
    begin
        return 1
    end
    
    -- process drop first
    -- process according to the publisher
    
    if (@islocalpub = 1)
    begin
        
        -- local publisher
        -- drop user on target db created from the certificate login if it exists
        
        select @user = N'REPLCERTUSR_' + @certname
        if exists (select u.name, c.name
                    from sys.database_principals u join master.sys.certificates c
                        on u.sid = c.sid
                    where u.name = @user
                        and c.name = @certname)
        begin
            select @cmd = N'drop user ' + quotename(@user)
            exec (@cmd)
            if (@@error != 0)
                return 1
        end
    end
    
    -- drop login
    
    select @login = N'REPLCERTLOGIN_' + @certname
    if exists (select u.name, c.name
                from master.sys.server_principals u join master.sys.certificates c
                    on u.sid = c.sid
                where u.name = @login
                    and c.name = @certname)
    begin
        select @cmd = N'use master drop login ' + quotename(@login)
        exec (@cmd)
        if (@@error != 0)
            return 1
    end
    
    -- drop the certificate now
    
    if exists (select name from master.sys.certificates where name = @certname)
    begin
        select @cmd = N'use master drop certificate ' + quotename(@certname)
        exec (@cmd)
        if (@@error != 0)
            return 1
    end
    
    -- if we are in drop mode
    -- we are done
    
    if (@mode = 2)
        return 0
    
    -- create the certificate on from backup dump
    -- server should provide a mechanism to copy certificates across databases
    
    exec @retcode = sys.sp_MSget_setup_paths @data_path = @mssql_data_path output
    if (@retcode != 0 or @@error != 0 or @mssql_data_path = N'')
        return 1
    select @cmd = N'use master create certificate ' + quotename(@certname) + ' from file = N''' + sys.fn_replreplacesinglequote(@mssql_data_path + N'\DATA\' + @certname) + '.cer'' '
    exec (@cmd)
    if (@@error != 0)
        return 1
    
    -- create login on from the certificate on master
    
    select @login = N'REPLCERTLOGIN_' + @certname
    select @cmd = N'use master create login ' + quotename(@login) + ' from certificate ' + quotename(@certname)
    exec (@cmd)
    if (@@error != 0)
        return 1
    
    -- process according to the publisher
    
    if (@islocalpub = 1)
    begin
        
        -- local publisher
        -- create user on target db from the login
        
        select @user = N'REPLCERTUSR_' + @certname
        select @cmd = N'create user ' + quotename(@user) + ' for login ' + quotename(@login)
        exec (@cmd)
        if (@@error != 0)
            return 1
        
        -- and grant the db level authentication for this certificate user
        
        select @cmd = N'grant connect to ' + quotename(@user)
                                + N' grant authenticate to ' + quotename(@user)
        exec (@cmd)
        if (@@error != 0)
            return 1
    end
    else
    begin
        
        -- remote publisher - target is master
        -- and grant the server level authentication
        
        select @cmd = N'grant authenticate server to ' + quotename(@login)
        exec (@cmd)
        if (@@error != 0)
            return 1
    end
    
    -- delete the certificate dump
    
    select @cmd = N'del "' + sys.fn_escapecmdshellsymbolsremovequotes(@mssql_data_path + N'\DATA\' + @certname) + '.cer" '
    exec master.dbo.xp_cmdshell @cmd, no_output
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_addsynctriggerscore (Procedure)
sp_link_publication (Procedure)
sp_MSrepltrigredefine (Procedure)
sp_unlink_publication_internal (Procedure)
       



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