如题所述
1. é¦å
å¨ SQL Server æå¡å¨çº§å«ï¼å建ç»éå¸æ·ï¼create loginï¼
create login dba with password='sqlstudy', default_database=mydb
ç»éå¸æ·å为ï¼âdbaâï¼ç»éå¯ç ï¼âsqlstudyâï¼é»è®¤è¿æ¥å°çæ°æ®åºï¼âmydbâãè¿æ¶åï¼dba å¸æ·å°±å¯ä»¥è¿æ¥å° SQL Server æå¡å¨ä¸äºãä½æ¯æ¤æ¶è¿ä¸è½è®¿é®æ°æ®åºä¸ç对象ï¼ä¸¥æ ¼ç说ï¼æ¤æ¶ dba å¸æ·é»è®¤æ¯ guest æ°æ®åºç¨æ·èº«ä»½ï¼å¯ä»¥è®¿é® guest è½å¤è®¿é®çæ°æ®åºå¯¹è±¡ï¼ã
è¦ä½¿ dba å¸æ·è½å¤å¨ mydb æ°æ®åºä¸è®¿é®èªå·±éè¦ç对象ï¼éè¦å¨æ°æ®åº mydb ä¸å»ºç«ä¸ä¸ªâæ°æ®åºç¨æ·âï¼èµäºè¿ä¸ªâæ°æ®åºç¨æ·â æäºè®¿é®æéï¼å¹¶ä¸æç»éå¸æ·âdbaâ åè¿ä¸ªâæ°æ®åºç¨æ·â æ å°èµ·æ¥ãä¹ æ¯ä¸ï¼âæ°æ®åºç¨æ·â çååå âç»éå¸æ·âçååç¸åï¼å³ï¼âdbaâãå建âæ°æ®åºç¨æ·âå建ç«æ å°å ³ç³»åªéè¦ä¸æ¥å³å¯å®æï¼
2. å建æ°æ®åºç¨æ·ï¼create userï¼ï¼
create user dba for login dba with default_schema=dbo
并æå®æ°æ®åºç¨æ·âdbaâ çé»è®¤ schema æ¯âdboâãè¿æå³çç¨æ·âdbaâ å¨æ§è¡âselect * from tâï¼å®é ä¸æ§è¡çæ¯ âselect * from dbo.tâã
3. éè¿å å ¥æ°æ®åºè§è²ï¼èµäºæ°æ®åºç¨æ·âdbaâæéï¼
exec sp_addrolemember 'db_owner', 'dba'
æ¤æ¶ï¼dba å°±å¯ä»¥å ¨æ管çæ°æ®åº mydb ä¸ç对象äºã
å¦ææ³è®© SQL Server ç»éå¸æ·âdbaâ访é®å¤ä¸ªæ°æ®åºï¼æ¯å¦ mydb2ãå¯ä»¥è®© sa æ§è¡ä¸é¢çè¯å¥ï¼
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go
æ¤æ¶ï¼dba å°±å¯ä»¥æ两个æ°æ®åº mydb, mydb2 ç管çæéäºï¼
4. ç¦ç¨ãå¯ç¨ç»éå¸æ·ï¼
alter login dba disable
alter login dba enable
5. ç»éå¸æ·æ¹åï¼
alter login dba with name=dba_tom
æ示ï¼å¨ SQL Server 2005 ä¸ä¹å¯ä»¥ç» sa æ¹åã ãSQL Server 2005 å®å ¨æ§å¢å¼ºï¼ç»è¶ 级ç¨æ· sa æ¹åã
6. ç»éå¸æ·æ¹å¯ç ï¼
alter login dba with password='sqlstudy.com'
7. æ°æ®åºç¨æ·æ¹åï¼
alter user dba with name=dba_tom
8. æ´æ¹æ°æ®åºç¨æ· defult_schemaï¼
alter user dba with default_schema=sales
9. å é¤æ°æ®åºç¨æ·ï¼
drop user dba
10. å é¤ SQL Serverç»éå¸æ·ï¼
drop login dba追é®
create login dba with password='sqlstudy', default_database=mydb
ç»éå¸æ·å为ï¼âdbaâï¼ç»éå¯ç ï¼âsqlstudyâï¼é»è®¤è¿æ¥å°çæ°æ®åºï¼âmydbâãè¿æ¶åï¼dba å¸æ·å°±å¯ä»¥è¿æ¥å° SQL Server æå¡å¨ä¸äºãä½æ¯æ¤æ¶è¿ä¸è½è®¿é®æ°æ®åºä¸ç对象ï¼ä¸¥æ ¼ç说ï¼æ¤æ¶ dba å¸æ·é»è®¤æ¯ guest æ°æ®åºç¨æ·èº«ä»½ï¼å¯ä»¥è®¿é® guest è½å¤è®¿é®çæ°æ®åºå¯¹è±¡ï¼ã
è¦ä½¿ dba å¸æ·è½å¤å¨ mydb æ°æ®åºä¸è®¿é®èªå·±éè¦ç对象ï¼éè¦å¨æ°æ®åº mydb ä¸å»ºç«ä¸ä¸ªâæ°æ®åºç¨æ·âï¼èµäºè¿ä¸ªâæ°æ®åºç¨æ·â æäºè®¿é®æéï¼å¹¶ä¸æç»éå¸æ·âdbaâ åè¿ä¸ªâæ°æ®åºç¨æ·â æ å°èµ·æ¥ãä¹ æ¯ä¸ï¼âæ°æ®åºç¨æ·â çååå âç»éå¸æ·âçååç¸åï¼å³ï¼âdbaâãå建âæ°æ®åºç¨æ·âå建ç«æ å°å ³ç³»åªéè¦ä¸æ¥å³å¯å®æï¼
2. å建æ°æ®åºç¨æ·ï¼create userï¼ï¼
create user dba for login dba with default_schema=dbo
并æå®æ°æ®åºç¨æ·âdbaâ çé»è®¤ schema æ¯âdboâãè¿æå³çç¨æ·âdbaâ å¨æ§è¡âselect * from tâï¼å®é ä¸æ§è¡çæ¯ âselect * from dbo.tâã
3. éè¿å å ¥æ°æ®åºè§è²ï¼èµäºæ°æ®åºç¨æ·âdbaâæéï¼
exec sp_addrolemember 'db_owner', 'dba'
æ¤æ¶ï¼dba å°±å¯ä»¥å ¨æ管çæ°æ®åº mydb ä¸ç对象äºã
å¦ææ³è®© SQL Server ç»éå¸æ·âdbaâ访é®å¤ä¸ªæ°æ®åºï¼æ¯å¦ mydb2ãå¯ä»¥è®© sa æ§è¡ä¸é¢çè¯å¥ï¼
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go
æ¤æ¶ï¼dba å°±å¯ä»¥æ两个æ°æ®åº mydb, mydb2 ç管çæéäºï¼
4. ç¦ç¨ãå¯ç¨ç»éå¸æ·ï¼
alter login dba disable
alter login dba enable
5. ç»éå¸æ·æ¹åï¼
alter login dba with name=dba_tom
æ示ï¼å¨ SQL Server 2005 ä¸ä¹å¯ä»¥ç» sa æ¹åã ãSQL Server 2005 å®å ¨æ§å¢å¼ºï¼ç»è¶ 级ç¨æ· sa æ¹åã
6. ç»éå¸æ·æ¹å¯ç ï¼
alter login dba with password='sqlstudy.com'
7. æ°æ®åºç¨æ·æ¹åï¼
alter user dba with name=dba_tom
8. æ´æ¹æ°æ®åºç¨æ· defult_schemaï¼
alter user dba with default_schema=sales
9. å é¤æ°æ®åºç¨æ·ï¼
drop user dba
10. å é¤ SQL Serverç»éå¸æ·ï¼
drop login dba追é®
å·²ç»è¯è¿äºä¸è¡åï¼ä¹åå°±æ¯ççè¿ç¯æç«
追çæè¯è¯å¨åè¯ä½
create login dba with password='sqlstudy', default_database=master
温馨提示:答案为网友推荐,仅供参考