<p><br></p><p><br></p><p><br></p><p>맨날 헷갈린다.</p><p>정리해 봤다.</p><p><br></p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #6a9955;">--&nbsp;https://dbrang.tistory.com/142</span></div><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">DATABASE</span>&nbsp;db;</div><div><span style="color: #569cd6;">GO</span>&nbsp;&nbsp;</div><br><div><span style="color: #6a9955;">--#&nbsp;login&nbsp;추가</span></div><div><span style="color: #569cd6;">EXEC</span>&nbsp;sp_addlogin&nbsp;<span style="color: #ce9178;">'motolies'</span>,&nbsp;<span style="color: #ce9178;">'defaultPassword1!'</span>,&nbsp;<span style="color: #ce9178;">'db'</span>;</div><div><span style="color: #569cd6;">ALTER</span>&nbsp;<span style="color: #569cd6;">LOGIN</span>&nbsp;motolies&nbsp;<span style="color: #569cd6;">WITH</span>&nbsp;<span style="color: #569cd6;">PASSWORD</span>&nbsp;=&nbsp;<span style="color: #ce9178;">'changePassword'</span>&nbsp;<span style="color: #569cd6;">UNLOCK</span>,&nbsp;<span style="color: #569cd6;">CHECK_POLICY</span>&nbsp;=&nbsp;<span style="color: #569cd6;">OFF</span>,&nbsp;<span style="color: #569cd6;">CHECK_EXPIRATION</span>&nbsp;=&nbsp;<span style="color: #569cd6;">OFF</span>;</div><br><div><span style="color: #6a9955;">--#&nbsp;사용자&nbsp;생성(해당&nbsp;데이터베이스에&nbsp;대한&nbsp;접속&nbsp;권한&nbsp;부여)</span></div><div><span style="color: #569cd6;">USE</span>&nbsp;db;</div><div><span style="color: #569cd6;">GO</span></div><div><span style="color: #569cd6;">SET</span>&nbsp;<span style="color: #569cd6;">ANSI_PADDING</span>&nbsp;<span style="color: #569cd6;">ON</span></div><div><span style="color: #569cd6;">GO</span></div><div><span style="color: #569cd6;">EXEC</span>&nbsp;sp_grantdbaccess&nbsp;<span style="color: #ce9178;">'motolies'</span>;</div><div><span style="color: #569cd6;">GO</span></div><br><div><span style="color: #6a9955;">--#&nbsp;사용자&nbsp;역할&nbsp;부여</span></div><div><span style="color: #569cd6;">EXEC</span>&nbsp;sp_addrolemember&nbsp;<span style="color: #ce9178;">'db_owner'</span>,&nbsp;<span style="color: #ce9178;">'motolies'</span>;</div><div><span style="color: #569cd6;">GO</span></div><br></div><p><br></p><p><br></p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #6a9955;">--#&nbsp;로그인을&nbsp;위한&nbsp;추가</span></div><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">LOGIN</span>&nbsp;motolies&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WITH</span>&nbsp;<span style="color: #569cd6;">PASSWORD</span>&nbsp;=&nbsp;<span style="color: #ce9178;">'1234'</span>;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">GO</span>&nbsp;&nbsp;</div><br><div><span style="color: #6a9955;">--#&nbsp;로그인&nbsp;후&nbsp;db&nbsp;접속&nbsp;사용자(아래와&nbsp;같이&nbsp;많들면&nbsp;public&nbsp;권한으로&nbsp;만들어&nbsp;진다)</span></div><div><span style="color: #569cd6;">USE</span>&nbsp;db;</div><div><span style="color: #569cd6;">CREATE</span>&nbsp;USER&nbsp;motolies&nbsp;FOR&nbsp;<span style="color: #569cd6;">LOGIN</span>&nbsp;motolies;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">GO</span>&nbsp;&nbsp;</div><br><div><span style="color: #6a9955;">--#&nbsp;사용자&nbsp;역할&nbsp;부여</span></div><div><span style="color: #569cd6;">EXEC</span>&nbsp;sp_addrolemember&nbsp;<span style="color: #ce9178;">'db_owner'</span>,&nbsp;<span style="color: #ce9178;">'motolies'</span>;</div><div><span style="color: #569cd6;">GO</span></div><br><div><span style="color: #6a9955;">/*</span></div><div><span style="color: #6a9955;">보통의&nbsp;경우&nbsp;db_owner&nbsp;권한만&nbsp;줘도&nbsp;된다</span></div><br><div><span style="color: #6a9955;">--&nbsp;https://kinanadel.blogspot.com/2019/07/mssql-microsoft-sql-server_12.html</span></div><div><span style="color: #6a9955;">db_accessadmin&nbsp;=&nbsp;엔진에&nbsp;접속할&nbsp;윈도우&nbsp;인증과&nbsp;SQL&nbsp;Server&nbsp;로그인을&nbsp;수정할&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_backupoperator&nbsp;=&nbsp;데이터베이스&nbsp;백업&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_datareader&nbsp;=&nbsp;테이블의&nbsp;모든&nbsp;데이터를&nbsp;볼&nbsp;수&nbsp;있는&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_datawriter&nbsp;=&nbsp;테이블에&nbsp;데이터&nbsp;수정&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_ddladmin&nbsp;=&nbsp;데이터베이스의&nbsp;모든&nbsp;DDL&nbsp;관련&nbsp;명령을&nbsp;실행할&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_denydatareader&nbsp;=&nbsp;데이터베이스에&nbsp;있는&nbsp;모든&nbsp;테이블의&nbsp;데이터를&nbsp;볼&nbsp;수&nbsp;있는&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_denydatawriter&nbsp;=&nbsp;데이터베이스에&nbsp;있는&nbsp;모든&nbsp;테이블의&nbsp;데이터를&nbsp;수정할&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_owner&nbsp;=&nbsp;데이터베이스의&nbsp;모든&nbsp;설정과&nbsp;관리&nbsp;작업을&nbsp;수행할&nbsp;수&nbsp;있는&nbsp;권한.</span></div><div><span style="color: #6a9955;">db_secutiryadmin&nbsp;=&nbsp;멤버&nbsp;권한을&nbsp;제한할&nbsp;수&nbsp;있는&nbsp;권한.</span></div><div><span style="color: #6a9955;">public&nbsp;=&nbsp;디폴트로&nbsp;부여되는&nbsp;최소한의&nbsp;권한.</span></div><div><span style="color: #6a9955;">*/</span></div><br><br><div><span style="color: #6a9955;">--#&nbsp;로그인&nbsp;제거</span></div><div><span style="color: #569cd6;">DROP</span>&nbsp;<span style="color: #569cd6;">LOGIN</span>&nbsp;motolies;&nbsp;&nbsp;</div><div><span style="color: #569cd6;">GO</span>&nbsp;</div><br><div><span style="color: #6a9955;">--#&nbsp;사용자&nbsp;제거</span></div><div><span style="color: #569cd6;">DROP</span>&nbsp;USER&nbsp;motolies;</div><div><span style="color: #569cd6;">GO</span></div><br><div><span style="color: #6a9955;">--#&nbsp;로그인&nbsp;제거&nbsp;강제(삭제도&nbsp;한&nbsp;번에&nbsp;가능)</span></div><div><span style="color: #569cd6;">USE</span>&nbsp;<span style="color: #569cd6;">master</span>;</div><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;DROP_LOGIN_FORCE</div><div>(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;@loginNameToDrop&nbsp;<span style="color: #569cd6;">NVARCHAR</span>(<span style="color: #b5cea8;">500</span>)</div><div>)</div><div><span style="color: #569cd6;">AS</span></div><div><span style="color: #569cd6;">BEGIN</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;https://stackoverflow.com/questions/4965260/drop-sql-login-even-while-logged-in</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;sessionsToKill&nbsp;CURSOR&nbsp;<span style="color: #569cd6;">FAST_FORWARD</span>&nbsp;FOR</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #dcdcaa;">session_id</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;sys.dm_exec_sessions</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;login_name&nbsp;=&nbsp;@loginNameToDrop</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">OPEN</span>&nbsp;sessionsToKill</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;@sessionId&nbsp;<span style="color: #569cd6;">INT</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;@statement&nbsp;<span style="color: #569cd6;">NVARCHAR</span>(<span style="color: #b5cea8;">200</span>)</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FETCH</span>&nbsp;<span style="color: #569cd6;">NEXT</span>&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;sessionsToKill&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;@sessionId</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHILE</span>&nbsp;<span style="color: #dcdcaa;">@@FETCH_STATUS</span>&nbsp;=&nbsp;<span style="color: #b5cea8;">0</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">BEGIN</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">PRINT</span>&nbsp;<span style="color: #ce9178;">'Killing&nbsp;session&nbsp;'</span>&nbsp;+&nbsp;<span style="color: #569cd6;">CAST</span>(@sessionId&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NVARCHAR</span>(<span style="color: #b5cea8;">20</span>))&nbsp;+&nbsp;<span style="color: #ce9178;">'&nbsp;for&nbsp;login&nbsp;'</span>&nbsp;+&nbsp;@loginNameToDrop</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;@statement&nbsp;=&nbsp;<span style="color: #ce9178;">'KILL&nbsp;'</span>&nbsp;+&nbsp;<span style="color: #569cd6;">CAST</span>(@sessionId&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NVARCHAR</span>(<span style="color: #b5cea8;">20</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">EXEC</span>&nbsp;sp_executesql&nbsp;@statement</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FETCH</span>&nbsp;<span style="color: #569cd6;">NEXT</span>&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;sessionsToKill&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;@sessionId</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CLOSE</span>&nbsp;sessionsToKill</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DEALLOCATE</span>&nbsp;sessionsToKill</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">PRINT</span>&nbsp;<span style="color: #ce9178;">'Dropping&nbsp;login&nbsp;'</span>&nbsp;+&nbsp;@loginNameToDrop</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;@statement&nbsp;=&nbsp;<span style="color: #ce9178;">'DROP&nbsp;LOGIN&nbsp;['</span>&nbsp;+&nbsp;@loginNameToDrop&nbsp;+&nbsp;<span style="color: #ce9178;">']'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">EXEC</span>&nbsp;sp_executesql&nbsp;@statement</div><br><div><span style="color: #569cd6;">END</span></div><br><div><span style="color: #6a9955;">--#&nbsp;프로시저&nbsp;실행</span></div><div><span style="color: #569cd6;">exec</span>&nbsp;<span style="color: #569cd6;">master</span>.dbo.DROP_LOGIN_FORCE&nbsp;<span style="color: #ce9178;">'motolies'</span>;</div><br></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>


[MSSQL] 사용자 추가 삭제