계정 관리
9. 계정확인 (SQL 인증 계정확인)
sp_helplogins -> 각 데이터베이스의 로그인 및 연관된 사용자에 관한 정보를 제공
EXEC sp_helplogins [ [ @LoginNamePattern = ] 'login' ]
예제 -> EXEC sp_helplogins 'Victoria'
[@LoginNamePattern =] 'login'
로그인 이름 login은 sysname이며 기본값은 NULL입니다. login은 지정된 경우, 반드시 존재하고 있어야 합니다. login을 지정하지 않은 경우에는 모든 로그인에 관한 정보가 반환
반환 코드 값 -> 0(성공) 또는 1(실패)
----------------------------------------------------------------------------------------------------
보안->로그인->서버역활->security Adminisreator -> 서버의 로그인를 관리
----------------------------------------------------------------------------------------------------
10. 계정삭제 -> 비번을 물어보거나 하지 않는다 (SQL 인증)
sp_droplogin [ @loginame = ] 'login'
로그인을 제거하여 이 로그인 이름을 사용하여 SQL Server에 액세스하지 못하도록 합니다.
예제 -> EXEC sp_droplogin 'Albert'
보안->로그인->서버역활->security Adminisreator -> 서버의 로그인를 관리
10.1 계정삭제(윈도우 인증)
sp_revokelogin
sp_grantlogin 또는 sp_denylogin으로 만든 Microsoft Windows NT® 사용자 또는 그룹에 대한 로그인 항목을 Microsoft® SQL Server™에서 제거합니다.
예제-> EXEC sp_revokelogin 'KHCLASS402_169\test' <--컴퓨터명\계정명 (관리자일때만 가능)
sp_revokelogin [ @loginame = ] 'login'
[@loginame =] 'login'-> Windows NT 사용자 또는 그룹의 이름 login은 sysname이며 기본값은 없습니다. login은 Domain\User 형식의 기존 Windows NT 사용자 이름 또는 그룹이 될 수 있습니다.
=======================================================================================
(SQL인증과 windows인증의 차이점 )
=======================================================================================
1. SQL인증은 외부에서 직접 DB로 접근하는 것이다.
2. windows인증은 우선 외부에서 windows로 그리고 windows에서 다시 SQL로 인증을 거쳐 가는 것이다.
여기서 win 인증을 하려면 windows의 admin계정으로 접속을 하고 admin계정으로
SQL에 들어가려면 SQL에서 계정을 임시로 만들어 (계정이름이 같을수도 다를 수도 있다.) 그 계정으로
SQL 서버로 들어가게 된다.
---------------------------------------------------------------------------------------
---------------
고정서버 역할
---------------
sp_addsrvrolemember -> 고정 서버 역할의 구성원으로서 로그인을 추가합니다.
구문
sp_addsrvrolemember [ @loginame = ] 'login'
, [ @rolename = ] 'role'
문제) 고정서버를 적어라
답 : sysadmin , securityadmin , serveradmin, setupadmin , processadmin, diskadmin , dbcreator, bulkadmin
예제 EXEC sp_addsrvrolemember 'user01' , 'sysadmin' -> user01 계정을 sysadmin서버역활에 추가한다.
--sysadmin 역활에 추가된 경우 -> 계정추가도 가능
--------------------------
고정서버 역할 구성원 확인
--------------------------
sp_helpsrvrolemember -> 고정 서버 역할의 구성원에 관한 정보를 반환합니다.
구문
sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]
예제: EXEC sp_helpsrvrolemember 'sysadmin' -> sysadmin 서버역할의 구성원을 보여준다
---------------------
고정 서버 역할 삭제
---------------------
sp_dropsrvrolemember -> 고정 서버 역할에서 로그인 또는 사용자나 그룹을 제거
구문
sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role'
예제 : EXEC sp_dropsrvrolemember 'user01' , 'sysadmin' -> user01 계정을 sysadmin서버역활에 삭제한다
------------------------------------
현재 데이터베이스에 액세스 권한 허용
------------------------------------
서버차원에서 엑세스 권한을 부여하는 과정 -> 로그인계정추가
데이터베이스마다 엑세스 권한을 부여하는 과정 -> 사용자계정추가
sp_grantdbaccess
로그인 또는 사용자 또는 그룹에 대한 <<현재>> 데이터베이스에 보안 계정을 추가하며, 그 데이터베이스에서 동작을 수행할 수 있는 권한을 부여할 수 있도록 합니다.
구문
sp_grantdbaccess [@loginame =] 'login'
[,[@name_in_db =] 'name_in_db' [OUTPUT]]
사용자명->데이터베이스에서명 , 로그인명 ->서버에서의 명
로그인명 / 사용자명을 따로 줄수있다. (windows인증에서는 서로 다를수 있다)
현재 데이터베이스를 맞추고 (master 인지, pubs인지 확인)
예제: EXEC sp_grantdbaccess 'user01','user01'
-----------------------
현재 데이터베이스 변경
-----------------------
USE A_company -> 현재 데이터 베이스을 A_company 로 바꾼다
-----------------------------------------
현재 데이터베이스에 역할 구성원 추가
-----------------------------------------
sp_addrolemember
현재 데이터베이스의 기존 데이터베이스 역할의 구성원으로서 보안 계정을 추가합니다.
구문
sp_addrolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
예문 : EXEC sp_addrolemember 'db_owner','user01' ->현재 데이터베이스에서 user01을 dbo로 승격하여 올려준다
현재 데이터베이스에서는 관리자와 같은 권한을 가진다 db_owner
----------------------------------------
현재 데이터베이스에 역할 구성원 삭제
----------------------------------------
sp_droprolemember
현재 데이터베이스의 Microsoft® SQL Server™ 역할에서 보안 계정을 제거합니다.
구문
sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
예문 : EXEC sp_droprolemember 'db_owner','user01'
----------------------------------------
현재 데이터베이스에 액세스 권한 취소
----------------------------------------
sp_revokedbaccess
현재 데이터베이스에서 보안 계정을 제거합니다.
구문
sp_revokedbaccess [ @name_in_db = ] 'name'
예문: EXEC sp_revokedbaccess 'user01'
-----------------------------------------
현재 데이터베이스의 역할 구성원 확인
-----------------------------------------
sp_helprolemember
현재 데이터베이스에 있는 역할의 구성원에 관한 정보를 반환합니다.
구문
sp_helprolemember [ [ @rolename = ] 'role' ]
예제 : EXEC sp_helprolemember
-----------------------------------------
현재 데이터베이스의 사용자 계정 확인
-----------------------------------------
sp_helpuser
현재 데이터베이스의 데이터베이스 역할, Microsoft® SQL Server™ 사용자 및 Microsoft Windows NT® 사용자에 관한 정보를 보고합니다.
구문
sp_helpuser [ [ @name_in_db = ] 'security_account' ]
예문 : EXEC sp_helpuser
================================================================================
================================================================================
문제1) 옆사람의 이름을 SQL server 로그인 계정에 추가 합니다.
-> EXEC sp_addlogin '계정명','비번'
문제2) 위에서 등록된 로그인 계정을 A_company 데이터 베이스에 엑세스 할 수 있도록 만듭니다.
-> use A_company
-> EXEC sp_grantdbaccess '계정명','로그인명'
문제3) 위에서 등록된 로그인 계정을 A_company 데이터 베이스의 dbo로 만듭니다.
-> use A_company
-> EXEC sp_addrolemember 'db_owner','계정명'
(현재 데이터베이스를 항상 확인)
문제4) 위에서 등록된 로그인 계정을 SQL Server 관리자로 만듭니다.
-> EXEC sp_addsrvrolemember '계정명','sysadmin'
=================================================================================
=================================================================================
--------------------------------------
SQL server에서 등록된 로그인 계정 확인
--------------------------------------
EXEC sp_helplogins
EXEC sp_helplogins '계정명'
EXEC sp_helplogins 'sa'
----------------------------------------------
현재 데이터베이스에서 등록된 사용자 계정 확인
----------------------------------------------
USE A_company <--A_company 라는 데이터베이스위치
EXEC sp_helpuser
-----------------------
고정서버 역할 확인
-----------------------
EXEC sp_helpsrvrole
-------------------------
고정서버 역할 구성원 확인
-------------------------
EXEC sp_helpsrvrolemember 'sysadmin'
---------------------------
현재 데이터베이스 역할 확인
---------------------------
USE A_company <--A_company 라는 데이터베이스위치
EXEC sp_helprole
-----------------------------------
현재 데이터베이스 역할 구성원 확인
-----------------------------------
USE A_company <--A_company 라는 데이터베이스위치
EXEC sp_helprolemember 'db_owner'
-------------------------------------
SQL server의 데이터베이스 이름 확인
-------------------------------------
EXEC sp_helpdb
---------------------------------
현재 데이터 베이스의 데이블 확인
---------------------------------
EXEC sp_tables
------------------------------
SQL server의 버전정보 확인
-----------------------------
select @@version
--------------------------------
현재 연결된 SQL Server 이름확인
--------------------------------
select @@servername
고정 서버 역할
만들 수 없는 고정 서버 역할은 서버 수준에서 정의되며 각 데이터베이스 외부에 존재합니다. 사용자를 고정 서버 역할에 추가하려면 사용자에게 SQL Server나 Microsoft Windows NT® 4.0 또는 Windows® 2000 로그인 계정이 있어야 합니다. 고정 서버 역할의 모든 구성원은 다른 로그인을 추가할 수 있습니다.
중요 BUILTIN\Administrators 그룹의 구성원인 Windows NT 4.0 또는 Windows 2000 사용자는 자동으로 sysadmin 고정 서버 역할의 구성원이 됩니다.
다음 테이블에서는 고정 서버 역할을 설명합니다.
| 고정 서버 역할 | 설명 |
|---|---|
| sysadmin | SQL Server에서 모든 작업을 수행합니다. 이 역할의 권한은 모든 다른 고정 서버 역할에 걸쳐 배치됩니다. |
| serveradmin | 서버 차원의 설정을 구성합니다. |
| setupadmin | 연결된 서버를 추가/제거하고 sp_serveroption 등의 일부 시스템 저장 프로시저를 실행합니다. |
| securityadmin | 서버 로그인을 관리합니다. |
| processadmin | SQL Server 인스턴스에서 실행 중인 프로세스를 관리합니다. |
| dbcreator | 데이터베이스를 만들고 대체합니다. |
| diskadmin | 디스크 파일을 관리합니다. |
| bulkadmin | BULK INSERT 문을 실행합니다. |
securityadmin은 이제 sysadmin 역할의 구성원이 아닌 모든 사용자에 대해 sp_password 저장 프로시저를 수행할 권한이 있습니다.
bulkadmin 고정 서버 역할은 BULK INSERT 문을 실행할 권한을 갖습니다. bulkadmin 역할의 구성원은 다른 고정 서버 역할의 모든 구성원과 마찬가지로 해당 역할에 다른 로그인을 추가할 수 있습니다. 그러나 BULK INSERT 문 실행과 관련된 보안상(BULK INSERT 문을 실행하려면 네트워크의 모든 데이터와 서버가 실행 중인 컴퓨터에 대해 읽기 권한 필요) bulkadmin 역할의 구성원이 다른 사용자에게 사용 권한을 허가하는 것은 바람직하지 않습니다. bulkadmin 역할을 사용하면 sysadmin 고정 서버 역할의 구성원이 sysadmin 권한을 허가하지 않고 BULK INSERT 문 실행에 필요한 작업을 위임할 수 있습니다. bulkadmin 역할 구성원은 BULK INSERT 문을 실행할 수 있지만 데이터를 삽입할 테이블에서 INSERT 권한을 갖고 있어야 합니다.
[출처] [펌] MSSQL 권한관리|작성자 사랑굳