보미네

--dynamic 쿼리가 몹시 귀찮은 경우
--다른 컬럼으로 index 를 타고, 필터조건만 처리 할 경우 유용한 쿼리 로직

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int
,c1 int
,c2 int
)
go

insert into tblx values(1,1,null),(2,null,2),(3,3,3), (4, null,null)

-- @c1 의 값이 null 이면 필터하지 않고, 값이 있으면 값으로 뿌린다.
-- 보통 다음과 같은 쿼리를 짜는데, 이는 null 값에 문제를 일으킨다.
-- 아래 문제를 해결하는 것이 미션 1 이다.

declare @c1 int = null

select *
  from tblx
 where c1 = case when @c1 is null then c1 else @c1 end
go

-- 해결방안

숙제임 (iq 90)

-- @c1 의 값이 9999 이면 필터하지 않고, 값이 있으면 값으로 출력하며, null 이면 null 만 출력한다. 
-- 이것이 미션 2 이다.

숙제임 (iq 100)

--dynamic 쿼리가 몹시 귀찮은 경우
--다른 컬럼으로 index 를 타고, 필터조건만 처리 할 경우 유용한 쿼리 로직

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int
,c1 int
,c2 int
)
go

insert into tblx values(1,1,null),(2,null,2),(3,3,3), (4, null,null)

-- @c1 의 값이 null 이면 필터하지 않고, 값이 있으면 값으로 뿌린다.
-- 보통 다음과 같은 쿼리를 짜는데, 이는 null 값에 문제를 일으킨다.
-- 아래 문제를 해결하는 것이 미션 1 이다.

declare @c1 int = null

select *
  from tblx
 where c1 = case when @c1 is null then c1 else @c1 end
go

-- 해결방안

declare @c1 int = null

select *
  from tblx
 where (c1 = @c1 or 1=case when @c1 is null  then 1 else 0 end)
go

-- @c1 의 값이 9999 이면 필터하지 않고, 값이 있으면 값으로 출력하며, null 이면 null 만 출력한다. 
-- 이것이 미션 2 이다.

declare @c1 int = 9999

select *
  from tblx
 where (c1 = @c1 or 1=case when @c1 = 9999  then 1 else 0 end) or (c1 is null and 1=case when @c1 is null then 1 else 0 end)
go

-- 복합 필터 조건
declare @c1 int = null
declare @c2 int = null

select *
  from tblx
 where ((c1 = @c1 or 1=case when @c1 = 9999  then 1 else 0 end) or (c1 is null and 1=case when @c1 is null then 1 else 0 end))
   and ((c2 = @c2 or 1=case when @c2 = 9999  then 1 else 0 end) or (c2 is null and 1=case when @c2 is null then 1 else 0 end))
go

 select *
  from tbly
 where (a = @col1 or @col1 is null)

create login loginname with password = 'password'
go

use northwind
go

create user loginname for login loginname
go

GRANT EXECUTE TO loginname
go

EXEC master..sp_dropsrvrolemember @loginame = N'coupon', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = N'coupon', @rolename = N'sysadmin'

에러는 이렇게 잡으세요~

create proc a
with execute as owner
as
select 'dynamic sql'
go





Denali ?
Denali 에서는 아래와 같은 기능이 추가 되었습니다.

Custom server roles

Mike Walsh blog post: http://bit.ly/AB_Denali_MikeWalsh

파일을 다운로드 해서

AP_PROC_SCRIPT.sql

실행하시면 되고, 몇몇 변수는 수정해야 합니다.
purge_times 숫자만큼 남기고 모두 삭제 됩니다.
sql agnet 를 이용해 일정 시간 간격으로 백업 하시면 개발자나 DBA 혹은 예상치 못한 오류로 부터 개발코드를 지킬 수 있습니다.

restore database mirrortest from disk ='c:\backup\full.bak'
with norecovery

restore log mirrortest from disk ='c:\backup\log.bak'
with norecovery

alter database mirrortest
set partner off

restore database mirrortest with recovery



http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

restore database JoyCoupon from disk ='C:\back\JOYDBSQLFB_JOYCOUPON_FFFFFFFFFF_20110327_203730.fbak'
with norecovery, password ='PRD00012323541234',
move 'matjoy' to 'd:\db\matjoy.mdf',
move 'matjoy_log' to 'c:\db\matjoy_log.ldf'
, replace
go

restore log JoyCoupon from disk ='c:\back\JOYDBSQLLB_JOYCOUPON_LLLLLLLLLL_20110327_203806.lbak'
with norecovery
go

restore log JoyCoupon from disk ='c:\back\JOYDBSQLLB_JOYCOUPON_LLLLLLLLLL_20110327_204000.lbak'
with norecovery
go

http://support.microsoft.com/kb/246133 2000 의 로그인 카피

http://support.microsoft.com/kb/918992 2005 이상의 경우 로그인 카피

sp_change_users_login 'Update_One', 'kindsaint','kindsaint'


-- ------------------------------------------------------------------------------------------------------------------------
-- 우리는 sql 2008 에서 2008r2 로 데이터베이스 미러링을 통해 업그레이드 하려고 한다.
-- ------------------------------------------------------------------------------------------------------------------------
-- 1. 같은 windows 계정으로 sql 을 스타트 시킨다.  ** 참고로 반드시 계정으로 스타트 시켜야 한다.
-- 2. 방화벽을 내린다. wf.msc 에서 모든 방화벽 제거 (혹은 mirroring endpoint port 만 open 우리는 7022)
-- 3. ping 이 가는지 test 한다. (양방향)
-- 4. endpoint 대상 포트로 telnet 테스트를 한다.  서버 관리자에서 telnet 기능을 활성화 한다. (시간이 좀 걸릴 수 도 있음) 
       endpoint 설정하고 할것...-_- 바봉....
-- 5. host 추가 한다. FQDN 으로 설정해야 한다.
-- 6. cmd 를 ctrl shift alt + enter 로 관리자 모드로 시작한 다음 sql alias 에 설정된 이름을 등록한다.
-- 7. notepad c:\windows\system32\drivers\etc\hosts 수정한다.
-- 8. sqlservermanager10.msc 에서 alias 는 32bit 64bit 모두 설정한다.
-- 9. 데이터베이스 이름에는 특수문자가 없어야 한다. ㅠ.ㅠ _ (언더바 있으면 에러난다. 씨봉)
-- ------------------------------------------------------------------------------------------------------------------------
-- 대상 데이터베이스 fullbackup
-- 대상 데이터베이스 logbackup 수행
--backup database minsouk to disk ='c:\backup\minsouk.full'
--backup log minsouk to disk ='c:\backup\minsouk_log.bak'
-- ------------------------------------------------------------------------------------------------------------------------
-- norecovery 모드로 log 까지 복구한다.
--restore database minsouk from disk ='c:\backup\minsouk.full'
--with norecovery,
--move 'minsouk' to 'c:\backup\minsouk.mdf',
--move 'minsouk_log' to 'c:\backup\minsouk_log.ldf'
--, replace
--go
--restore log minsouk from disk ='c:\backup\minsouk_log.bak'
--with norecovery
--go
-- ------------------------------------------------------------------------------------------------------------------------
--endpoint 확인하는 쿼리 2번째 쿼리의 결과로 나오는 name 이 미러링용 endpoint 이다.
--SELECT type_desc, port FROM sys.tcp_endpoints;
--SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
--drop endpoint endpoint_mirroring
-- 만약 삭제를 해야 한다면 drop endpoint endpointname
-- ------------------------------------------------------------------------------------------------------------------------
-- 양쪽 서버 ssms 에서 endpoint 생성
--CREATE ENDPOINT endpoint_mirroring
--STATE = STARTED
--AS TCP ( LISTENER_PORT = 7022 )
--FOR DATABASE_MIRRORING (ROLE=PARTNER, ENCRYPTION=DISABLED);
--telnet 에서 telnet bammabak 7022 하면 접속되면서 화면이 모두 사라진다. (정상적이면, 에러면, 에러떨어지지요)
--ALTER DATABASE minsouk
--SET PARTNER ='TCP://bammabak:7022'
-- ------------------------------------------------------------------------------------------------------------------------
-- 파트너 쪽에서 mirroring 제거시
--alter database minsouk
--set partner off
-- 복구시
--restore database minsouk with recovery
-- ------------------------------------------------------------------------------------------------------------------------
-- 삭제
--use master
--go
--alter database minsouk set single_user with rollback immediate
--go
--drop database minsouk
--go
-- ------------------------------------------------------------------------------------------------------------------------

 




restore headeronly from disk ='G:\mirror_init\CYBERSQL_SQLFB_BANK_FFFFFFFFFFFFFFF_20110817_004805.fbak'
with password ='D'

restore filelistonly from disk ='G:\mirror_init\CYBERSQL_SQLFB_BANK_FFFFFFFFFFFFFFF_20110817_004805.fbak'
with password ='D'

restore database bank from disk = 'G:\mirror_init\CYBERSQL_SQLFB_BANK_FFFFFFFFFFFFFFF_20110817_004805.fbak'
with norecovery ,
move 'bank' to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\bank.mdf',
move 'bank_log' to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\bank_log.ldf',
password = 'D'

restore log bank from disk ='G:\mirror_init\CYBERSQL_SQLLB_BANK_LLLLLLLLLLLLLLL_20110817_005812.lbak'
with norecovery

CREATE LOGIN USER1 WITH PASSWORD=N'1234', DEFAULT_DATABASE=[MASTER]
GO

ALTER LOGIN USER1 ENABLE
GO

USE [DB1]
GO

ALTER USER [USER1] WITH DEFAULT_SCHEMA=[USER1]
GO

EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'USER1', 'USER1'
GO

(0개 행이 영향을 받음)
메시지 916, 수준 14, 상태 1, 프로시저 usp_a, 줄 5
현재 보안 컨텍스트로는 서버 보안 주체 "minsouk-PC\minsouk"이(가) 데이터베이스 "DB2"에 액세스할 수 없습니다.
위와 같은 오류가 난다면 다음 옵션을 설정하세요!


CREATE DATABASE DB1
GO
CREATE DATABASE DB2
GO

CREATE LOGIN [USER_EXEC] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[MASTER]
GO

ALTER LOGIN [USER_EXEC] ENABLE
GO

USE [DB1]
GO
CREATE USER [USER_EXEC] FOR LOGIN [USER_EXEC]
GO

USE DB1
GO

CREATE TABLE TBLX
(IDX INT)
GO

CREATE PROC USP_A
WITH EXECUTE AS OWNER
AS
SELECT * FROM DB1.DBO.TBLX
SELECT * FROM DB2.DBO.TBLX
GO

USE DB2
GO

CREATE TABLE TBLX
(IDX INT)
GO

USE DB1
GO

GRANT EXECUTE ON USP_A TO USER_EXEC
GO

ALTER DATABASE DB1 SET TRUSTWORTHY ON

--ALTER DATABASE DB1 SET TRUSTWORTHY off
GO

http://msdn.microsoft.com/en-us/library/ms187861.aspx

sql 8.0 은 meta data 를 고쳐야 하고
sql 9.0 이상은 설정이 가능하다.

http://technet.microsoft.com/ko-kr/library/ms189077.aspx


DROP DATABASE DB1
GO

DROP DATABASE DB2
GO

SELECT 'KILL '+CAST(SPID AS VARCHAR) FROM SYSPROCESSES WHERE SPID > 50 AND LOGINAME ='DB1USER'

DROP LOGIN DB1USER
GO

CREATE DATABASE DB1
GO
CREATE DATABASE DB2
GO

CREATE LOGIN [DB1USER] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[MASTER]
GO

ALTER LOGIN DB1USER ENABLE
GO

DENY VIEW ANY DATABASE TO DB1USER
GO

USE DB1
GO

EXEC SP_CHANGEDBOWNER 'DB1USER'
GO

-- db1user 로 로그인해보면 자기 데이터베이스만 보임
-- 아래는 참고

--grant view any database to db1user
--deny view any database to db1user
--grant view any database to public
--revoke view any database from public




열이글~

use master
go

create database db_1
create database db_2
create database db_3

CREATE LOGIN db_2 WITH PASSWORD='1', DEFAULT_DATABASE=db_2

use db_2
go
ALTER AUTHORIZATION ON DATABASE::db_2 to db_2

use db_3
go
ALTER AUTHORIZATION ON DATABASE::db_3 to db_2

use master
go

deny view any database to db_2

-- REVOKE VIEW ANY DATABASE FROM public 방법보다 발전(?)함.

-- 하나 계정으로 여러개 원하는 DB 목록 보이게 가능
-- 단점 하나 DB에 여러 계정 설정 불가능. master, tempdb 가 목록에 나옴.