MSSQL에서 "스키마에 대한 SELECT 권한이 거부되었습니다" 오류 해결 방법

SQL Server(MSSQL)에서 작업 중에 "스키마에 대한 SELECT 권한이 거부되었습니다"라는 오류가 발생했다면, 이는 사용자가 특정 테이블, 뷰, 또는 스키마에 대한 SELECT 권한을 가지고 있지 않아서 발생하는 문제입니다. 이 오류를 해결하기 위해서는 적절한 권한을 부여하거나, 권한 설정을 수정해야 합니다. 이 글에서는 MSSQL에서 SELECT 권한 거부 오류를 해결하는 방법에 대해 상세히 설명합니다.

 

 

스키마 ○○○에 대한 SELECT 권한이 거부되었습니다.

 

 

 

---

1. 오류의 원인: SELECT 권한 부족

 

"스키마에 대한 SELECT 권한이 거부되었습니다" 오류는 사용자가 데이터베이스 내에서 특정 테이블이나 뷰에 접근할 때 해당 객체에 대한 SELECT 권한을 가지지 않았을 때 발생합니다. SQL Server에서는 권한 관리가 중요하며, 각 객체에 대해 어떤 사용자에게 어떤 권한을 부여할지 세밀하게 관리할 수 있습니다.

이 오류가 발생하는 주된 이유는 다음과 같습니다.

 

• 사용자가 특정 테이블, 뷰, 또는 스키마에 대한 SELECT 권한을 부여받지 않았을 경우
• 권한이 제대로 상속되지 않았거나 누락된 경우
• 스키마나 테이블에 대해 명시적으로 권한이 설정되지 않았을 경우

이러한 권한 설정 문제로 인해, 사용자는 데이터를 조회할 수 없게 됩니다.

 

 

 

---

2. 해결 방법: SELECT 권한 부여

 

이 오류를 해결하기 위해서는 사용자가 필요한 SELECT 권한을 해당 스키마나 테이블에 대해 부여해야 합니다. SQL Server에서는 GRANT 명령어를 사용하여 권한을 부여할 수 있습니다.

 

 

 

---

2.1. 스키마에 대한 SELECT 권한 부여

 

특정 스키마에 대한 SELECT 권한을 부여하려면, GRANT SELECT ON SCHEMA 구문을 사용하여 해당 스키마에 대한 권한을 부여합니다. 예를 들어, Sales 스키마에 대해 사용자 user1에게 SELECT 권한을 부여하려면 다음과 같은 SQL 쿼리를 실행합니다.

GRANT SELECT ON SCHEMA::Sales TO user1;

이 쿼리는 Sales 스키마 내 모든 테이블에 대해 user1에게 SELECT 권한을 부여합니다.

 

 

 

---

2.2. 특정 테이블에 대한 SELECT 권한 부여

 

특정 테이블에 대해 SELECT 권한을 부여하려면, GRANT SELECT ON [스키마명].[테이블명] 구문을 사용하여 해당 테이블에 권한을 부여합니다. 예를 들어, Sales.Orders 테이블에 대해 user1에게 SELECT 권한을 부여하려면 다음과 같이 쿼리를 작성합니다.

GRANT SELECT ON Sales.Orders TO user1;

이렇게 하면 Sales 스키마의 Orders 테이블에 대해 user1이 SELECT 작업을 할 수 있게 됩니다.

 

 

 

반응형

 

---

3. 권한 부여 확인

 

권한이 제대로 부여되었는지 확인하려면, SQL Server에서 제공하는 fn_my_permissions 함수를 사용하여 사용자가 해당 테이블이나 스키마에 대해 어떤 권한을 가지고 있는지 조회할 수 있습니다.

-- 사용자가 특정 테이블에 대해 가지고 있는 권한을 확인
SELECT * FROM fn_my_permissions('Sales.Orders', 'OBJECT');

이 쿼리를 실행하면 user1이 Sales.Orders 테이블에 대해 어떤 권한을 가지고 있는지 확인할 수 있습니다. SELECT 권한이 제대로 부여되었는지 확인해보세요.

 

 

 

---

4. 역할 기반 권한 부여

 

SQL Server에서는 특정 역할에 사용자를 추가하여 권한을 일괄적으로 부여할 수도 있습니다. 예를 들어, db_datareader 역할을 사용하면 데이터베이스 내 모든 테이블에 대한 **읽기 권한(SELECT 권한)**을 부여할 수 있습니다.

 

4.1. db_datareader 역할 추가

db_datareader 역할은 데이터베이스 내 모든 테이블에 대해 SELECT 권한을 자동으로 부여합니다. 사용자를 이 역할에 추가하려면 다음과 같은 SQL 명령어를 사용합니다.

-- 사용자에게 db_datareader 역할 부여
EXEC sp_addrolemember 'db_datareader', 'user1';

이렇게 하면 user1은 db_datareader 역할을 통해 데이터베이스 내 모든 테이블에 대해 SELECT 권한을 가지게 됩니다.

 

 

 

---

5. 사용자 계정과 로그인 확인

 

사용자 계정이 데이터베이스에 적절히 연결되어 있는지 확인하는 것도 중요합니다. 사용자가 로그인과 데이터베이스 사용자로 연결되지 않은 경우 권한이 제대로 적용되지 않을 수 있습니다.

 

5.1. 사용자와 로그인 연결

만약 사용자가 로그인과 데이터베이스 사용자로 매핑되지 않은 경우, 다음과 같은 SQL 명령어를 사용하여 사용자를 로그인에 연결할 수 있습니다.

-- 사용자 계정과 로그인 연결
ALTER USER [사용자이름] WITH LOGIN = [로그인이름];

이 명령어는 데이터베이스 사용자와 로그인 계정을 연결시켜, 권한이 제대로 작동하도록 합니다.

 

 

 

---

6. 중복된 권한 문제 해결

경우에 따라, 사용자가 이미 다른 방법으로 권한을 부여받았거나, 중복된 권한 설정이 있을 수 있습니다. 이럴 때는 불필요한 권한을 제거하고, 올바른 권한만을 부여하는 것이 중요합니다.

 

6.1. 권한 제거

사용자에게 부여된 권한을 제거하려면 REVOKE 명령어를 사용하여 권한을 철회할 수 있습니다.

-- 특정 테이블에 대한 SELECT 권한 철회
REVOKE SELECT ON Sales.Orders FROM user1;

이렇게 하면 user1은 더 이상 Sales.Orders 테이블에 대해 SELECT 작업을 수행할 수 없습니다.

 

 

 

---

7. 그 외 방법 : 명령어를 대신하여 dbms 툴로 간단하게 해결 

'데이터베이스 역할 멤버 자격 중 deny~ 역할 체크 해제'

1. 보안 - 로그인 - 접속했던 계정 마우스 오른쪽 버튼 클릭 후 '속성' 클릭

 

2. '사용자 매핑'탭 진입

 

3. 해당 데이터베이스 선택 후,

   아래에 '데이터베이스 역할 멤버 자격'

   목록 중 db_denydatareader, db_denydatawriter 체크 해제

4. 확인 후 해당 계정으로 접속 후 쿼리 다시 실행.

 

 

 

---

 

 

MSSQL에서 "스키마에 대한 SELECT 권한이 거부되었습니다" 오류는 사용자가 필요한 SELECT 권한을 부여받지 않았을 때 발생합니다. 이 문제를 해결하기 위해서는 GRANT SELECT 명령어를 사용하여 해당 스키마나 테이블에 권한을 부여하거나, 역할을 통해 권한을 설정할 수 있습니다. 또한, 사용자 계정과 로그인 연결을 확인하고, 중복된 권한 문제를 해결하는 방법도 필요합니다.

이와 같은 절차를 통해 권한 문제를 해결하고, 데이터베이스에 정상적으로 접근할 수 있도록 할 수 있습니다.