金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > MSSQL数据库迁移之用户名处理方法,使用动态SQ

MSSQL数据库迁移之用户名处理方法,使用动态SQ

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-26 19:31

/*实则作者也搞不懂为啥要用SQL来创造,明明SQL Server有图形化创造数据库多方便啊!*/
USE master;

CREATE PROC sp_job_LoadPorc
@command     nvarchar(陆仟),         --异步调用的贮存进度依然要实践的 Transact-SQL 语句
@DatabaseName sysname=NULL,        --在老大数据库中试行作业步骤,私下认可在脚下数据库中
@jobdelay      int=5,                --当前时光后的略阿秒钟实施,该值大于等于5
@jobid        uniqueidentifier OUTPUT --定义的学业编号
AS
--作业名称,作业的实践时间
DECLARE @jobname sysname,@time int
SELECT @jobname=N'不时作业'
        +N'_'+LEFT(HOST_NAME(),40)
        +N'_'+CONVERT(char(19),GETDATE(),120)
        +N'_'+CAST(NEWID() as varchar(36)),
    @jobdelay=CASE
        WHEN ISNULL(@jobdelay,0)<5 THEN 5
        ELSE @jobdelay+1 END,
    @time=REPLACE(CONVERT(char(8),
        DATEADD(Second,@jobdelay,GETDATE()),
        108),':','')

USE tempdb

/* sp_help_revlogin script
** Generated 06 24 2009 1:40PM on WORKGROU-B1XTVC */
DECLARE @pwd sysname
-- Login: hxtest
SET @pwd = CONVERT (varbinary(256), 0x0100CF4E7D342B359438E4BCCA72E6C83F44FCCF30C8016286DE2B359438E4BCCA72E6C83F44FCCF30C8016286DE)
EXEC master..sp_addlogin '520web', @pwd, @sid = 0x1738BB6AD0CD24498F67FB5589E8EDCB, @encryptopt = 'skip_encryption'
......

​DECLARE @sqlstr nvarchar(max)
/*概念一个变量*/
DECLARE @database_name nvarchar(20) = 'MyDB';
/*这边输入要求创立的数目库名*/
DECLARE @file_name_d nvarchar(200) = CONVERT(sysname, SERVERPROPERTY('InstanceDefaultDataPath'))
/*此间是运用SQL Server的serverproperty这一个函数来获取当前实例的默许数据文件位置和日志文件地方*/
DECLARE @file_name_l nvarchar(200) = CONVERT(sysname, SERVERPROPERTY('InstanceDefaultLogPath'))
/*微软官方参数介绍

IF DB_ID('MyDB') IS NOT NULL
    BEGIN
        PRINT 'Database ' +@database_name+' already exists';  --决断MyDB是不是留存,存在的话就杀死,作者也不领会怎么要那样写
        DROP DATABASE MyDB; --依然手动删除吧,不使用脚本删除
    END

--数据库名
IF DB_ID(@DatabaseName) IS NULL
    SET @DatabaseName=DB_NAME()

IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name='20秒二回的作业')
    EXEC msdb.dbo.sp_delete_job @job_name='20秒一遍的课业'

第二步

ELSE
    
    BEGIN
        SET @sqlstr= 'CREATE DATABASE'+' '+@database_name+' '
        SET @sqlstr =  @sqlstr +'ON'
        SET @sqlstr =  @sqlstr +'('    
        SET @sqlstr =  @sqlstr +'NAME = '+' '+@database_name+'_dat,'
        SET @sqlstr =  @sqlstr +'FILENAME = '+''''+@file_name_d+''+@database_name+'.mdf'','
        SET @sqlstr =  @sqlstr +'SIZE = 10,'
        SET @sqlstr =  @sqlstr +'MAXSIZE = 50,'
        SET @sqlstr =  @sqlstr +'FILEGROWTH = 5'
        SET @sqlstr =  @sqlstr +')'
        SET @sqlstr =  @sqlstr +'LOG ON'
        SET @sqlstr =  @sqlstr +'(   NAME = '+' '+@database_name+'_log,'
        SET @sqlstr =  @sqlstr +'FILENAME = '+''''+@file_name_l+''+@database_name+'.ldf'','
        SET @sqlstr =  @sqlstr +'SIZE = 5MB,'
        SET @sqlstr =  @sqlstr +'MAXSIZE = 25MB,'
        SET @sqlstr =  @sqlstr +'FILEGROWTH = 5MB'
        SET @sqlstr =  @sqlstr +');'
        Print (@sqlstr) -- 要是不想直接施行,使用应用print参数先把命令打印出来
        --exec (@sqlstr);   -- 推行直接下面生成的动态SQL
        --Print 'Datbaase '+@database_name +' has been created using default data and log location in the server configuration!!'
        /*打字与印刷已经成立成功的数据库名字*/
        --Print 'Data file location = '+@file_name_d+@database_name+'.mdf';
        /*打字与印刷创立的数据库的数据文件路线*/
金沙棋牌app手机下载,        --Print 'Log file location = '+@file_name_l+@database_name+'.ldf';
        /*打字与印刷创制的数据库的日志文件路线*/
    END

--检查是还是不是存在同名作业,存在则删除
IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname)
    EXEC msdb..sp_delete_job @job_name=@jobname

--定义成立作业
DECLARE @jobid uniqueidentifier
EXEC msdb.dbo.sp_add_job
    @job_name = N'20秒一回的作业',
    @job_id = @jobid OUTPUT

SP_DEFAULTDB 'cyiyun','DB_WAYUP'

--定义作业
EXEC msdb.dbo.sp_add_job
    @job_name = @jobname,
    @delete_level =3,           --作业实施后自动删除
    @job_id = @jobid OUTPUT

--定义作业步骤
DECLARE @sql nvarchar(400),@dbname sysname
SELECT @dbname=DB_NAME(),
    @sql=N'
        DECLARE @dt datetime
        SET @dt=DATEADD(Minute,1,GETDATE())
        WHILE @dt>GETDATE()
        BEGIN
            IF EXISTS(SELECT * FROM sysobjects WHERE name=''tb_log'')            
                INSERT tb_log VALUES(CONVERT(char(8),GETDATE(),108))
            ELSE
                SELECT dt=CONVERT(char(8),GETDATE(),108) INTO tb_log
            WAITFOR DELAY ''00:00:20''
        END'
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobid,
    @step_name = N'作业步骤名称',
    @subsystem = 'TSQL',
    @database_name=@dbname,
    @command = @sql

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmps教程tr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '

--定义作业步骤
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobid,
    @step_name = N'异步调用存款和储蓄进程依然实施Transact-SQL 语句',
    @subsystem = 'TSQL',
    @database_name=@DatabaseName,
    @command = @command

--创立调节
EXEC msdb..sp_add_jobschedule
    @job_id = @jobid,
    @name = N'每分钟实行的调解',
    @freq_type=4,
    @freq_interval=1,
    @freq_subday_type=0x4,
    @freq_subday_interval=1,
    @active_start_time = 000000

  • CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    PRINT 'DECLARE @pwd sysname'
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN -- NT login is denied access
    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
    PRINT @tmpstr
    END
    ELSE BEGIN -- NT login has access
    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
    PRINT @tmpstr
    END
    END
    ELSE BEGIN -- SQL Server authentication
    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    IF (@xstatus & 2048) = 2048
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
    ELSE
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
    PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  • ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
    END
    ELSE BEGIN
    -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  • ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
    END
    IF (@xstatus & 2048) = 2048
    -- login upgraded from 6.5
    SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    ELSE
    SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
    END
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

--创制调整
EXEC msdb..sp_add_jobschedule
    @job_id = @jobid,
    @name = N'异步调用存款和储蓄进程恐怕实行Transact-SQL 语句',
    @freq_type=1,
    @active_start_time = @time

--增多指标服务器
DECLARE @servername sysname
SET @servername=CONVERT(nvarchar(128),SERVERPROPERTY(N'ServerName'))
EXEC msdb.dbo.sp_add_jobserver
    @job_id = @jobid,
    @server_name = @servername
GO

sp_help_revlogin script ** Generated 06 24 2009 1:40PM on WORKGROU-B1XTVC */ DECLARE @pwd sysname -- Login: hxtest SET @pwd = CONVERT (varbinary(256), 0x0100CF4E7D342B359438E4BC...

--增加目的服务器
DECLARE @servername sysname
SET @servername=CONVERT(nvarchar(128),SERVERPROPERTY(N'ServerName'))
EXEC msdb.dbo.sp_add_jobserver
    @job_id = @jobid,
    @server_name = @servername

--等待2分钟后翻看结果
WAITFOR DELAY '00:02:00'
SELECT * FROM tb_log ORDER BY dt
GO

--删除测量试验
DROP TABLE tb_log
EXEC msdb.dbo.sp_delete_job @job_name='20秒二遍的课业'

/*--结果

dt       

15:08:00
15:08:20
15:08:40
--*/

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:MSSQL数据库迁移之用户名处理方法,使用动态SQ

关键词:

上一篇:没有了

下一篇:没有了