Migrate Encrypted Database to a New Server (with a new key)

Posted: May 29, 2014 | Author: | Filed under: SQL Server 2014 | Leave a comment 
 
/* Migrating SQL Server Databases that use Database Master Key
--http://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

--Step 1:
--Find databases:  Database Master Keys encrypted by the Service Master Key:

select name
from sys.databases
where is_master_key_encrypted_by_server = 1

--Step 2:
/*----Add one new password for DMK in the source server database
-- If you don’t know a valid password for the Database Master Key, you can create a new one. 
--(! Multiple passwords can encrypt the same Database Master Key)
*/
 
--USE [theDB]
--GO
 
--alter master key 
--add encryption by password = 'myPass1d2f3wStrong'
--go


--Step 3:
--Backup  theDB and restore it to a new SERVER.
--3-1:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
drop encryption by service master key
go

--3-2:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
add encryption by service master key
go




*/

How to Get Rid of the Black Screen of Remote Desktop After Successful Login

Posted: May 13, 2014 | Author: | Filed under: Uncategorized | Leave a comment

Click CTRL-ALT-END and Hit Cancel.
You may need to re login your remote desktop but I don’t need to this time.

Execute Stored Procedure with Parameters and Insert the Resultset Into a Table

Posted: May 9, 2014 | Author: | Filed under: SQL Server 2008 | Leave a comment 
 
 --Set parameters
declare	@did int = 1,
		@eid int =88

declare @sqltax varchar(1000);
select @sqltax = 'SET NOCOUNT ON;SET FMTONLY OFF; EXEC [mydb].[dbo].[getMytaxes]
	 @did = '+cast(@did as varchar(15))
	 +', @eid = '+cast(@eid as varchar(15))
		
EXEC ('SELECT *  INTO mytaxes  FROM  OPENROWSET(''SQLNCLI'', ''Server=MC12\MSSQL08R2;Trusted_Connection=yes;'',''' +  @sqltax + ''') AS t;')
;

select eid, sum(tax) as taxtotal 
into mytaxes2
from mytaxes
group by eid


Drop table mytaxes
Drop table mytaxes2