로그인 계정 생성
CREATE LOGIN MyNewAdminUser
WITH PASSWORD = N'password1!' MUST_CHANGE,
CHECK_POLICY = ON,
DEFAULT_DATaBASE = Ticket2000,
CHECK_EXPIRATION = ON;
Go
* MUST_CHANGE 로그인 비밀번호 강제 변경 알람.
sqlcmd error 메세지 - 원인 : 계정의 암호를 변경해야 합니다.
* MUST_CHANGE를 해제할 경우 "MUST_CHANGE가 설정되어 있으므로 CHECK_POLICY 및 CHECK_EXPIRATION 옵션을 해제할 수 없습니다." 오류가 발생됩니다.
CHECK_POLICY와 CHECK_EXPIRATION 를 주석으로 1회 실행되면 이후 정보를 수정할 수 있습니다.
로그인 계정 조회
select * from sys.server_principals where name like 'MyNew%' order by modify_date desc
사용자 계정 생성
CREATE USER [MyNewAdminUser] FOR LOGIN [MyNewAdminUser]
* 로그인 계정이 먼저 존재(만들어진 후)한 다음 생성함.
사용자 계정 조회
select * from sys.database_principals where name like 'MyNew%'
Client Command Tool(windows환경)
프로그램명 : sqlcmd.exe
windows 경로 : C:\Program Files\Microsoft SQL Server\110\Tools\Binn
로그인 확인 명령어 : sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1!
Client Command Tool 비밀번호 변경(windows환경)
비밀번호 변경 명령어 : sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1! -Z password1!_Change
--cmd.exe
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1! -Z password1!_Change
1>_
Client Command Tool(MacOS환경)
프로그램명 : sqlcmd(실행파일)
경로 : /opt/homebrew/Cellar/mssql-tools/17.10.1.1/bin
Repository : brew tap microsoft/mssql-release
* 다른 tap(Repository) 등록시 install 실행하면 프로그램 설치 오류가 발생할 수 있음.(2022/11/18 기준)
Steps to Reproduce:
1. ~/ brew tap microsoft/mssql-release
2. ~/ brew update
3. ~/ brew install microsoft/mssql-release/mssql-tools
Clinet Command Tool 비밀번호 변경(MacOS환경)
비밀번호 변경 명령어 :
sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1! -Z password1!_Change
또는 (버전마다 적용여부 -C 스위치 확인 필요)
sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1! -Z password1!_Change -C
--bash
/opt/homebrew/Cellar/mssql-tools/17.10.1.1/bin sqlcmd -S 172.25.5.211 -U MyNewAdminUser -P password1! -Z password1!_Change
1>_
데이터베이스 역할 멤버 자격
-- ex1)
USE TICKET2000 -- 사용할 Database 지정
go
ALTER ROLE [db_owner] ADD MEMBER [MyNewAdminUser]
-- ex2)
ALTER ROLE [db_datareader] ADD MEMBER [MyNewAdminUser]
* 개발자 멤버 역할 기준 : 개발자 계정 - [db_datareader | db_datawriter | db_owner | public]
참고 : db_owner ⊃ db_datareader, db_datawriter
데이터베이스 사용자정의역할 생성
USE [Ticket2000]
GO
----- 데이터베이스 수준 역할 조회
SELECT pr.type_desc,
pr.name,
ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
AND pr.name = 'role_devDDL'
ORDER BY pr.name,
type_desc;
-- 데이터베이스 역할 생성
CREATE ROLE [role_devDDL];
-- 데이터베이스 역할 멤버 추가
ALTER ROLE [role_devDDL] ADD MEMBER [MyNewAdminUser2]
-- 권한 부여
GRANT CREATE PROCEDURE TO [role_devDDL];
GRANT ALTER ON SCHEMA::[dbo] TO [role_devDDL];
GRANT INSERT ON SCHEMA::[dbo] TO [role_devDDL]
-- 테이블 변경 및 DROP 제외
DENY ALTER ANY SCHEMA TO [role_devDDL]
-- 권한 부여(예제)
GRANT CREATE PROCEDURE TO [newrole];
GRANT ALTER ON SCHEMA::[dbo] TO [newrole];
--REVOKE ALTER ON SCHEMA::[dbo] TO [newrole];
GRANT INSERT ON SCHEMA::[dbo] TO [newrole]
--REVOKE INSERT ON SCHEMA::[dbo] TO [newrole]
-- 프로시저생성및변경확인
CREATE OR ALTER Proc [dbo].[proc_test]
as
select top 1 OK = 'kkk'
-- 테이블생성및삭제확인
IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.tables WITH(NOLOCK) WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TMP_table' )
DROP TABLE TMP_table
GO
CREATE TABLE TMP_table
(
SEQ INT
)
ALTER TABLE TMP_table ADD GoodsName varchar(256) NOT NULL
ALTER TABLE TMP_table DROP COLUMN GoodsName
계정(사용자) 잠김 조회 및 잠금해제
USE TICKET2000 -- 사용할 Database 지정
go
select LOGINPROPERTY ( name , 'IsLocked' ) ,*
from sys.server_principals
where type = 'S'
go
ALTER LOGIN [계정명] WITH PASSWORD = ‘계정 비밀번호’ UNLOCK
* 관리자 권한으로 실행해야 함.
서버계정 접속거부 및 사용중지
서버계정 조회
USE [master]
GO
SELECT
ss_prin.[name]
, ss_prin.[principal_id] -- DB Catalog마다 다름 [default_database_name]로 설정된 값을 사용한다.
, ss_prin.[default_database_name]
, ss_prin.[is_disabled] -- 로그인(0/1 - 사용/사용안함)
, ss_per.[type] -- 기타 권한 퇴사자 목적은 COSQ 권한만 거부(D) 확인할 것
, ss_per.[state] -- 데이터베이스 엔진 연결 권한(G/D - 허용/거부)
, ss_per.[state_desc] -- 데이터베이스 엔진 연결 권한(G/D - 허용/거부)
FROM sys.server_permissions ss_per
INNER JOIN sys.server_principals ss_prin ON ss_per.grantee_principal_id = ss_prin.principal_id
WHERE ss_prin.[name] in('MyNewAdminUser', 'MyNewAdminUser2')
비활성
use [master]
go
DENY CONNECT SQL TO [MyNewAdminUser]
go
ALTER LOGIN [MyNewAdminUser] DISABLE
활성
use [master]
go
GRANT CONNECT SQL TO [MyNewAdminUser]
go
ALTER LOGIN [MyNewAdminUser] ENABLE
* 사용중지 후 1개월 이후 삭제 해야함.
DROP LOGIN [MyNewAdminUser]
DROP USER [MyNewAdminUser] -- DB CATALOG별로 삭제 해야함.
SQLAgent 권한 설정
use [msdb]
go
CREATE USER [MyNewAdminUser] FOR LOGIN [MyNewAdminUser]
go
EXEC sp_addrolemember [SQLAgentUserRole], [MyNewAdminUser]
EXEC sp_addrolemember [SQLAgentReaderRole], [MyNewAdminUser]
EXEC sp_addrolemember [SQLAgentOperatorRole], [MyNewAdminUser]
* 로컬 계정만 생성 및 수정 가능함. global 예약작업은 수정 불가함