'MS-SQL' 태그의 글 목록

'MS-SQL'에 해당되는 글 3건

2015.07.16 [MS-SQL] DB 계정 정보 가져오기
2012.07.03 [MS-SQL] 2008 플랜캐시(Plan Cache) 삭제하기
2009.06.23 [MS-SQL] [ Query ] 동적 쿼리 만들어 쓰기

[MS-SQL] DB 계정 정보 가져오기

프로그래밍/09.SQL / 2015. 7. 16. 11:13

DB 이전 작업을 진행하다 보면, DB의 계정 정보도 같이 옮겨 줘야하는 번거로움이 있다. 만약, 계정이 많지 않다면 DB를 옮겨 주고, 이전한 서버에서 하나하나 계정을 만들어 주면 된다. 하지만, 계정이 여러개이고, 각 계정의 비밀번호를 모른다면.... 대략 난감할 것 이다. 이런 경우에는 아래와 같은 방법으로 문제를 해결 할 수 있다.

1. 기존 DB 서버에 SQL Management Studio를 이용해 접속을 한다.

기존 DB서버에 접속후 SQL Management Studio를 실행한다. 그리고 쿼리 편집장을 열도록 한다.

2. 아래 쿼리를 복사해서 실행을 한다.

view plain copy to clipboard print ?

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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

** 위 쿼리를 실행하면 두개의 프로시져가 Master DB에 생성 되었을 것이다. (sp_hexadecimal , sp_help_revlogin)

3. 만들어진 프로시져 "sp_help_revlogin"을 실행한다.

view plain copy to clipboard print ?

EXEC sp_help_revlogin

** 프로시져를 실행하면 모든 계정 정보가 출력 될 것 이다. 출력 된 내용을 메모장에 저장해 둔다.

4. 이전하려는 DB서버 접속 후 SQL Management Studio를 실행한다.

출력된 목록 중 시스템 계정을 제외한 사용자 계정을 복사해, 이전하려는 DB 서버에 SQL Management Studio를 실행 후 내용을(사용자 계정 스크립트) 복사해서 실행한다.

5. 이전하려는 서버에서 계정이 잘 등록 되었는지, 로그인을 시도해 본다.

참고 : https://support.microsoft.com/en-us/kb/918992?wa=wsignin1.0

저작자표시 비영리 변경금지

Posted by happydong

, |

[MS-SQL] 2008 플랜캐시(Plan Cache) 삭제하기

프로그래밍/09.SQL / 2012. 7. 3. 11:34

MSSQL 2008 플랜캐시(Plan Cache) 삭제하기

출처 : http://ddoung2.tistory.com/242

엄청 오랫만에 글을 포스팅 하는것 같아요. 이래저래 정신없고 포스팅 할 여유가 없었네요 ~ ! 이번 글을 계기로 다시 한번 열심히 포스팅에 불을 -0-;;

오늘 울 DB Team.에 팀장으로 새로 오신 차주언 형님이 MSSQL 2008에 Adhoc 플랜캐시가 적재되는 bug가 있다하여 일일 작업으로 해당 Adhoc 플랜캐시를 삭제해줘야 한다고 하여, Job Schedule 로 만들어 보았습니다.

우선 아래 스크립트를 이용하여 현재 서버에 어떤 Adhoc 쿼리가 쌓여 있는지 한번 알아보았습니다. 제가 사용하고 있는 TEST 서버에서는 해당 쿼리를 실행시켰을때 사용하지도 않았던 이상한 update 쿼리문이 약 3000개가 적재되어 있더군요.

SELECT

text,

usecounts,

plan_handle

FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE USECOUNTS = 1

AND OBJTYPE = 'ADHOC'

그럼 해당 Adhoc 플랜캐시가 도대체 얼마만큼이나 메모리에 올라가 있는지 아래 쿼리로 확인해 보았습니다. 제 경우는 그렇게 크게 메모리에 올라가 있지 않았지만, 주언 형님의 말씀으로는 약 2GB이상 올라가 있는것도 보았다고 하더군요.

SELECT objtype AS [CacheType]

, count_big(*) AS [Total Plans]

, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]

, avg(usecounts) AS [Avg Use Count]

, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]

, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

자~~~ 그럼 플랜캐시가 얼마만큼 올라가 있고, 그 플랜캐쉬가 얼마만큼의 메모리를 사용하고 있는지 보았으니, 해당 플랜캐시를 주기적으로 삭제하기 위해서 MSSQL 스케쥴러에 등록해 보겠습니다.

아래 쿼리는 Adhoc 플랜캐시를 메모리상에서 지우는 쿼리를 프로시저로 만드는 쿼리 입니다.

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/*************************************************************************

** Name : MSSQL 2008 Server 에서AdHoc 플랜캐쉬삭제

** Desc : MSSQL 2008 Server 에서AdHoc 플랜캐쉬삭제

** EXEC :

** Auth : 이승연(gamebible)

** Date : 2011-01-31

**************************************************************************

** Change History

**************************************************************************

** Date: Author: Description:

** ---------- -------- ---------------------------------------

*************************************************************************/

ALTER PROCEDURE [dbo].[usp_SYSTEM_AdHoc_FREEPROCCACHE]

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/*******************************************************************

** 변수선언

*******************************************************************/

DECLARE @MAX INT;

DECLARE @PLAN_HANDLE VARCHAR (1000);

DECLARE @SQL_TEXT VARCHAR(MAX);

/*******************************************************************

** 변수선언

*******************************************************************/

/*******************************************************************

** 임시테이블삭제

*******************************************************************/

IF EXISTS(SELECT * FROM tempdb.sys.sysobjects where name like '#T_PLAN_HANDLE%')

DROP TABLE #T_PLAN_HANDLE;

/*******************************************************************

** 임시테이블삭제

*******************************************************************/

/*******************************************************************

** 임시테이블생성

*******************************************************************/

CREATE TABLE #T_PLAN_HANDLE (

IDX INT IDENTITY(1,1) ,

PLAN_HANDLE VARBINARY (1000)

);

/*******************************************************************

** 임시테이블생성

*******************************************************************/

/*******************************************************************

** AdHoc 플랜캐쉬핸들저장

*******************************************************************/

INSERT INTO #T_PLAN_HANDLE

SELECT

PLAN_HANDLE

--, *

FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE

USECOUNTS = 1

AND OBJTYPE = 'ADHOC'

/*******************************************************************

** AdHoc 플랜캐쉬핸들저장

*******************************************************************/

/*******************************************************************

** AdHoc 플랜캐쉬삭제

*******************************************************************/

SELECT @MAX = MAX(IDX) FROM #T_PLAN_HANDLE;

WHILE (@MAX > 0) BEGIN

SELECT @PLAN_HANDLE = SYS.FN_SQLVARBASETOSTR( PLAN_HANDLE) FROM #T_PLAN_HANDLE WHERE IDX = @MAX;

SET @SQL_TEXT = 'DBCC FREEPROCCACHE ('+CAST(@PLAN_HANDLE AS VARCHAR(8000))+')';

EXEC (@SQL_TEXT)

--print @sql_text

SET @MAX = @MAX - 1

END

/*******************************************************************

** AdHoc 플랜캐쉬삭제

*******************************************************************/

END

그리고 스케쥴러에 등록해 보아요.

USE msdb

EXEC sp_add_job @job_name = 'AdHoc 플랜캐쉬삭제'

EXEC sp_add_jobstep

@job_name = 'AdHoc 플랜캐쉬삭제' ,

@step_id = 1 ,

@step_name = 'AdHoc 플랜캐쉬삭제' ,

@subsystem = 'TSQL' ,

@command = 'EXEC [dbo].[usp_SYSTEM_AdHoc_FREEPROCCACHE];' ,

@database_name = 'master'

EXEC sp_add_jobschedule

@job_name = 'AdHoc 플랜캐쉬삭제' ,

@name = '일정' ,

@freq_type = 4 , -- 매일

@freq_interval = 1 , -- 한번

@active_start_time = 60000 -- HHMMSS

EXEC sp_add_jobserver

@job_name = 'AdHoc 플랜캐쉬삭제' ,

@server_name = N'(local)'

해당 스케쥴러는 매일 한번 새벽 6시에 돌아가게 해두었습니다.

저작자표시 비영리 변경금지

Posted by happydong

, |

[MS-SQL] [ Query ] 동적 쿼리 만들어 쓰기

프로그래밍/09.SQL / 2009. 6. 23. 15:13

ㅁ MS-SQL 동적 쿼리 만들어 쓰기.

동적쿼리를 만들어 쓰면 편한게 일단 조건을 상황에 따라 변경이 가능하고, 필용에 따라 필요한 데이터를 뽑기도 편한것 같다. 하지만...좀 쓰기 귀찮은게 있긴하다. 코드로 간단하게 알아 보자.

예제)

Query

DECLARE

@CurrentPage INT,

@PageSize INT,

@Missionidx INT,

@QuestionKind INT

SET @CurrentPage = 2;

SET @PageSize = 10;

SET @CurrentPage = @PageSize * (@CurrentPage-1);

DECLARE

@Query NVARCHAR(1000),

@Param NVARCHAR(100)

SET @Query = '

SELECT TOP(@PageSize) MemoIdx,UserIdx, MemoContent

FROM Memo

WHERE MemoIdx < (

SELECT ISNULL(MIN(A.MemoIdx),999999)

FROM (

SELECT TOP(@CurrentPage) MemoIdx

FROM Memo

ORDER BY MemoIdx DESC

) A

)

ORDER BY MemoIdx DESC';

SET @Param = '@PageSize INT, @CurrentPage INT';

EXECUTE sp_executesql @Query, @Param,

@PageSize,

@CurrentPage

위 쿼리는 간단하게 Memo테이블에서 Memo리스트를 가지고 오는 쿼리이다. 페이징 기능이 있는... 그래서 CurrentPage변수와 PageSize변수가 있는 것이다. 동적쿼리는 이렇게 Query라는 임의의 변수를 만들어 주고, 문자열로 지정해서 EXECUTE sp_executesql 함수를 이용해서 호출해 주면 되는 것이다.

Posted by happydong

, |

«이전 1 다음»

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

최고보다,최선을...

카테고리

달력

공지사항

태그목록

최근에 올라온 글

'MS-SQL'에 해당되는 글 3건

[MS-SQL] DB 계정 정보 가져오기

[MS-SQL] 2008 플랜캐시(Plan Cache) 삭제하기

[MS-SQL] [ Query ] 동적 쿼리 만들어 쓰기

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

링크

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역