June 15, 2012

sp_vupgrade_replsecurity_metadata (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_vupgrade_replsecurity_metadata()

MetaData:

 CREATE PROCEDURE sys.sp_vupgrade_replsecurity_metadata  
AS
BEGIN
SET NOCOUNT ON

DECLARE @retcode int,
@statement nvarchar(4000),
@database nvarchar(512),
@has_dbaccess bit,
@is_distributiondb bit,
@is_subscriberdb bit,
@is_upgraded bit
,@agentname nvarchar(100)
,@description nvarchar(100)
,@category_name sysname
,@dbname sysname

-- Security Check
IF ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0) = 0
BEGIN
-- "Only members of the sysadmin fixed server role can perform this operation."
RAISERROR(21089,16,-1)
RETURN 1
END

IF LOWER(DB_NAME()) != N'master'
BEGIN
-- "sp_vupgrade_replsecurity_metadata can only be executed in the master database."
RAISERROR (21482, 16, -1, 'sp_vupgrade_replsecurity_metadata', 'master')
RETURN 1
END

-- make sure user has already upgraded msdb
IF NOT EXISTS(SELECT *
FROM msdb.sys.columns
WHERE object_id = OBJECT_ID(N'msdb.dbo.sysjobsteps')
AND name = N'step_uid')
BEGIN
-- "The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s."
RAISERROR(21450, 16, -1, 'security meta-data', 'all', 'msdb database', 'sp_vupgrade_replsecurity_metadata')
RETURN 1
END

BEGIN TRANSACTION
SAVE TRANSACTION tr_upgrade_security_meta

DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT HAS_DBACCESS(name),
sys.fn_MSrepl_isdistdb(name),
N'[' + replace(name, N']', N']]') + N']',
name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( N'master' COLLATE DATABASE_DEFAULT,
N'tempdb' COLLATE DATABASE_DEFAULT,
N'msdb' COLLATE DATABASE_DEFAULT,
N'model' COLLATE DATABASE_DEFAULT
)
FOR READ ONLY

OPEN cursorDatabases

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname

WHILE @@FETCH_STATUS != -1
BEGIN
IF @has_dbaccess = 1
BEGIN
-- if this is a distribution db then upgrade dist meta-data
IF @is_distributiondb = 1
BEGIN
SELECT @statement = N'SELECT @is_upgraded = 0 ' +
N'IF EXISTS(SELECT * '+
N' FROM ' + @database + N'.sys.columns ' +
N' WHERE name = N''job_step_uid'') ' +
N'BEGIN ' +
N' SELECT @is_upgraded = 1 ' +
N'END'

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N'@is_upgraded bit OUTPUT',
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_upgraded = 1
BEGIN
SELECT @statement = @database + N'.[sys].[sp_MSupgrade_distdb_security_metadata]'

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
-- "The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s."
RAISERROR(21450, 10, -1, 'distributor security meta-data', @database, @database, 'sp_vupgrade_replsecurity_metadata') WITH NOWAIT
END
--
-- Add replmonitor refresh job for this distribution database
--
select @agentname = formatmessage (20811, @dbname)
select @description = @agentname
,@statement = N'exec dbo.sp_replmonitorrefreshjob '
if not exists (select * from msdb.dbo.sysjobs_view where name = @agentname collate database_default and
upper(originating_server collate database_default) = upper(convert(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
BEGIN
select @category_name = name FROM msdb.dbo.syscategories where category_id = 18
EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = 'TSQL',
@server = @@SERVERNAME,
@databasename = @dbname,
@description = @description,
@freqtype = 64, -- continuous
@freqsubtype = 0, -- unused
@freqsubinterval = 0, -- unused
@command = @statement,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name
if @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END

SELECT @statement = N'SELECT @is_subscriberdb = 0 ' +
N'IF EXISTS(SELECT * ' +
N' FROM ' + @database + N'.sys.objects ' +
N' WHERE name = N''MSsubscription_properties'') ' +
N'BEGIN ' +
N' SELECT @is_subscriberdb = 1 ' +
N'END ' +
N'SELECT @is_upgraded = 0 ' +
N'IF EXISTS(SELECT * ' +
N' FROM ' + @database + N'.sys.columns ' +
N' WHERE name = N''job_step_uid'') ' +
N'BEGIN ' +
N' SELECT @is_upgraded = 1 ' +
N'END'

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N'@is_subscriberdb bit OUTPUT, @is_upgraded bit OUTPUT',
@is_subscriberdb = @is_subscriberdb OUTPUT,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_subscriberdb = 1
BEGIN
IF @is_upgraded = 1
BEGIN
-- always assume this is subscriber db and attempt upgrade sub meta-data
SELECT @statement = @database + N'.[sys].[sp_MSupgrade_subdb_security_metadata]'

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
-- "The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s."
RAISERROR(21450, 10, -1, 'subscriber security meta-data', @database, @database, 'sp_vupgrade_replsecurity_metadata') WITH NOWAIT
END
END
END
ELSE
BEGIN
-- "Could not open database %s. Upgrade of replication %s could not be performed. Please rerun %s from %s database once the %s is accessible."
RAISERROR(21733, 10, -1, @database, 'security meta-data', 'sp_vupgrade_replsecurity_metadata', '[master]', @database) WITH NOWAIT
END

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname
END

CLOSE cursorDatabases
DEALLOCATE cursorDatabases

COMMIT TRANSACTION tr_upgrade_security_meta

RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_upgrade_security_meta
COMMIT TRANSACTION

RETURN 1
END

No comments:

Post a Comment

Total Pageviews