DB Library

원인

SQL Server Agent 서비스 제어는 SQL Server Agent Roles 권한이 부여되어 있거나 또는 sysadmin role 이 부여되어 있어야 합니다.
test_srv 계정은 SQL Sever Agent Roles 또는 sysadmin role 이 없습니다. 

해결방법

방법1. 
SSMS - Security - Logins - test_srv - User Mapping - msdb - SQLAgentUserRole 부여(필요에 따라 SQLAgentReaderRole, SQLAgentOperatorRole 부여 가능)
단, 운영체제 외부 리소스를 사용하기 위해서는 별도의 Credentials, SQL Server Proxy Accounts 를 설정할 수 있음.

test_srv 계정에 view database 권한 필요

만약 개체 존재 여부를 먼저 체크 하는 로직을 실행 한다면 아래 권한 추가

방법2. 
sysadmin role 부여 (권장하지 않음)


참고 : https://docs.microsoft.com/ko-kr/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2017

https://laigo.kr/497

Version - 5.7

-- 계정 생성

insert into user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject)
values('%', 'account_srv', password('StrongPassword'), '', '', '');

--권한
grant all privileges on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;
grant select, insert, update, delete, execute on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;

flush privileges;

--- 비밀 번호 변경
UPDATE mysql.user SET Password=PASSWORD('StrongPassword') WHERE User='account_srv' AND Host='%';

SET PASSWORD FOR 'account_srv'@'%' = PASSWORD('StrongPassword');

ALTER USER 'account_srv'@'%' identified by 'StrongPassword';

flush privileges;

-- 권한 확인
# show grants for 'user'@'접속위치';
show grants for 'account_srv'@'%';

-- 계정 삭제
# drop user '계정아이디'@'접속위치';
drop user 'account_srv'@'%';

-- 권한 삭제
# revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';
revoke all on *.* FROM 'account_srv'@'%';

Version - 8.0

-- 사용자 정보 조회 
SELECT user,authentication_string,plugin,host FROM mysql.user; 

-- 사용자 생성 
create user 'account_srv'@'%' identified by 'StrongPassword'; 

-- 권한 부여 
GRANT ALL PRIVILEGES ON *.* TO 'account_srv'@'%'; 
GRANT GRANT OPTION ON *.* TO 'account_srv'@'%'; 

-- 비밀번호 plugin 변경 
ALTER USER 'account_srv'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';

데이터베이스 단위로 권한 부여

--Example 
-- DB 명 : TEST_DB
-- Account : test_acc


use [TEST_DB]
GO
-- 권한 부여
GRANT SELECT TO [test_acc]
GRANT ALTER TO [test_acc]
GRANT DELETE TO [test_acc]
GRANT UPDATE TO [test_acc]
GRANT INSERT TO [test_acc]
GRANT CREATE TABLE TO [test_acc]
GO

-- 권한 제거
DENY UPDATE TO [test_acc]
DENY DELETE TO [test_acc]

스키마 단위로 권한 부여

--Example 
-- DB 명 : TEST_DB
-- 스키마 : dbo
-- Account : test_acc


USE [TEST_DB]
GO

CREATE USER [test_acc] FOR LOGIN [test_acc] WITH DEFAULT_SCHEMA=[dbo]

GRANT CONNECT TO test_acc AS [dbo]
GRANT SELECT TO test_acc AS [dbo]
GRANT DELETE TO test_acc AS [dbo]
GRANT UPDATE TO test_acc AS [dbo]
GRANT EXECUTE TO test_acc AS [dbo]
GRANT VIEW DEFINITION TO test_acc AS [dbo]
GO

지정 SP에 권한 부여

--Example
--DB 명 : TEST_DB
-- SP명 : sp_TEST_update
-- Account : Test_Acc

USE [TEST_DB]
GO

grant execute on sp_TEST_update to Test_Acc 
GO

일부 DB만 DB 리스트에 보여지도록

-- Database 소유자를 제외하고 DB 리스트가 보이지 않게 하는 형식

--Example
-- DB 명 : TEST_DB
-- Account :
---- TEST_Service : Database 리스트에 'TEST_DB' DB 확인 불가
---- TEST_Admin : Database 리스트에 'TEST_DB' DB 확인 가능

USE [master] 
GO

alter authorization on database::TEST_DB to TEST_Admin;
deny VIEW ANY DATABASE TO TEST_Service;
GO