에코의 내맘대로 일상생활

SQL Server 원격 연결 허용 오류 

IIS를 셋팅 후 처음 접속하게 됐을 때 SQL Server가 원격 연결을 허용하도록 구성되어 있는지 확인하십시오. 라고 오류가 뜬다. 그래서 셋팅을 설정해줘야하는데 

아래의 경로를 찾아가 

Sql Server Configuration Manager를 실행한다.

Sql Server 네트워크 구성에가서 TCP/IP 속성에 사용으로 변경해주고

IP주소 탭으로 이동하여 자신이 사용하고 있는 IP주소 (본인은 IP8에 있었음.)에 사용 활성을 '예'로 변경해준다.

 맨 밑에 IPALL 에는 TCP 포트를 1433 (MS SQL 기본 포트)를 적어주고 적용을 눌러 닫는다.

오른쪽 마우스 버튼을 클릭하여 다시 시작을 하여 재 실행해주고 

밑에 있는 SQL Server Browser도 실행이 안되어 있으면 실행해주자.

MS SQL로 로컬 접속을하여 속성에서 보안탭에 들어가 SQL Server 및 Windows 인증 모드(S) 로 선택해주면 된다.

마지막으로 방화벽에 들어가서

인바운드 규칙에서 새 인바운드 규칙을 만들어서 포트를 선택하고

아까 전에 입력한 MS SQL 기본 포트인 1433을 입력하고

다음 다음다음~~~~

자기가 지정하고 싶은 이름으로 보기편하게 입력하면 끝이다.

자 이제 접속을 하면 될것이다!

나는 왜 안되나 했더니 핸드폰으로 테더링을 하여 고정 IP가 아닌 유동 IP라 접속이 안됐던 것이다...

 OTL

핸드폰 테더링하여 로컬에서 IP주소로 SQL Server 접속가능하게 하시는 방법을 아시는분은 댓글로 남겨주시면 감사하겠습니다...

MS-SQL DB의 SP내용 검색하기 (sys.sql_modules) 

테이블 내에서 원하는 데이터를 검색할 땐 LIKE를 사용한다. 

하지만 접속한 데이터베이스의 저장프로시저(SP) 내에서 원하는 내용을 검색할땐? 

이럴 때 필요한게 sys.sql_modules 이다.

기본 형식은 아래의 예와 같다고 보면 된다.

실전에서 사용해보자.

아래의 Get_TestTable, Get_TestTable2 프로시저가 있다. 

각각의 프로시저 내용안에는 사용되고 있는 TestTable 있는데

여러 SP에서 TestTable이 사용되는 곳을 찾고 싶다면 

sys.sql_modules를 이용하여 검색하면 된다!!!

(LIKE와 동일하게 대소문자 상관없다)

** 참고 : SP - Get_TestTable, Get_TestTable2

MS-SQL 커서(Cursor) 사용하기 (행단위 검색)

MS SQL에서 행 단위로 검색해서 데이터를 가공하고 싶거나 처리하고 싶을 때 커서(Cursor)를 활용하면 편리하다.

예를 들면, 아래와 같이 여러행 중에 잘못된 데이터가 들어간 경우만 찾아서 지우고 싶을 때 Cursor를 활용한 경우다.

TestTable에는 아래와 같은 데이터가 존재한다.

1. Cursor에 사용될 변수를 설정한다.

2. 변수 Cursor를 설정하고

3. Cursor에 담길 데이터를 셋팅한다

4. 선언한 Cursor 변수의 이름으로 Cursor는 시작

5. Cursor 안에서 사용될 (반복문 안에서 사용될) 3. 데이터들의 Row에 해당되는 컬럼들을 위에서 셋팅한 변수로 설정

6. 반복문 시작

7. 데이터 처리 or 가공

8. 다음 행이 셋팅한 변수로 변경

9. Cursor 닫기

10. Cursor 리소스 해제

위에 예시로 사용된 Cursor처럼 원하는 Table를 조회하고 반복문 안에서 데이터를 처리 or 가공하면 된다.

** 주의해야할 점은 방대한 양의 데이터를 조회해서 Cursor를 사용할 때 처리속도가 느릴 수 있다는 점이다.

데이터 처리 or 가공시에는 아래와 같이 조회를 하여 데이터가 맞게 나오는지 확인 후 Cursor를 사용하자.

MS-SQL 로컬 접속 후 DB와 User 생성

SSMS 설치 후 Server에 접속하려면 기본적으로 설정되어 있는 로컬로 접속을 하면 된다.

서버 이름 : (localdb)\MSSQLLocalDB

인증 : Windows 인증

새로운 DB를 생성하고자 한다면 

연결된 DB서버에 데이터베이스 -> 새 데이터베이스를 클릭 후 데이터베이스 이름을 입력하고 생성을 해주면 된다.

생성 후 사용자를 생성하려면 보안탭에 들어가서

로그인 -> 새 로그인 클릭 후 로그인 이름, SQL Server인증에 암호, 암호확인, 암호 정책 강제 적용, 암호 만료 강제 적용을 체크 후 기본 데이터베이스를 설정해 주면 된다.

생성을 했다면 로그인탭에 생성한 유저가 들어와 있을것이다.

이제 이 유저한테 권한을 주면 되는데 

유저의 속성을 들어가서 줄 수 있다.

접속 권한 DB를 설정해주고

해당 유저가 어떤 기능까지 수행할지 셋팅해주면 끝나게 된다.

db_backupoperator : 백업권한

db_datareader : 사용자 테이블 읽기 권한 (SELECT)

db_datawriter : 사용자 테이블 데이터 추가, 삭제 변경 권한 (INSERT, DELETE, UPDATE)

db_ddladmin : DB에서 모든 DDL 명령 실행 권한 (CREATE, ALTER, DROP, RENAME, TRUNCATE)

db_denydatareader : DB내에 있는 사용자 테이블 읽기 권한

db_denydatawriter : DB내에 있는 사용자 테이블 데이터 추가, 삭제 변경 권한

db_owner : DB내에 있는 모든 권한

db_securityadmin : 멤버 자격 수정, 사용권한 관리 권한

확인하고 싶다면 방금 만들었던 해당 유저의 계정으로 접속 후 

DB를 확인하면 된다.