1. é¦å
å¨ SQL Server æå¡å¨çº§å«ï¼å建ç»éå¸æ·ï¼create loginï¼
--å建ç»éå¸æ·ï¼create loginï¼
create login dba with password='abcd1234@', default_database=mydb
ç»éå¸æ·å为ï¼âdbaâï¼ç»éå¯ç ï¼abcd1234@âï¼é»è®¤è¿æ¥å°çæ°æ®åºï¼âmydbâã è¿æ¶åï¼dba å¸æ·å°±å¯ä»¥è¿æ¥å° SQL Server æå¡å¨ä¸äºãä½æ¯æ¤æ¶è¿ä¸è½ 访é®æ°æ®åºä¸ç对象ï¼ä¸¥æ ¼ç说ï¼æ¤æ¶ dba å¸æ·é»è®¤æ¯ guest æ°æ®åºç¨æ·èº«ä»½ï¼ å¯ä»¥è®¿é® guest è½å¤è®¿é®çæ°æ®åºå¯¹è±¡ï¼ã
è¦ä½¿ dba å¸æ·è½å¤å¨ mydb æ°æ®åºä¸è®¿é®èªå·±éè¦çå¯¹è±¡ï¼ éè¦å¨æ°æ®åº mydb ä¸å»ºç«ä¸ä¸ªâæ°æ®åºç¨æ·âï¼èµäºè¿ä¸ªâæ°æ®åºç¨æ·â æäºè®¿é®æéï¼å¹¶ä¸æç»éå¸æ·âdbaâ åè¿ä¸ªâæ°æ®åºç¨æ·â æ å°èµ·æ¥ã ä¹ æ¯ä¸ï¼âæ°æ®åºç¨æ·â çååå âç»éå¸æ·âçååç¸åï¼å³ï¼âdbaâã å建âæ°æ®åºç¨æ·âå建ç«
æ å°å
³ç³»åªéè¦ä¸æ¥å³å¯å®æï¼
2. å建æ°æ®åºç¨æ·ï¼create userï¼ï¼
--为ç»éè´¦æ·å建æ°æ®åºç¨æ·ï¼create userï¼,å¨mydbæ°æ®åºä¸çsecurityä¸çuserä¸å¯ä»¥æ¾å°æ°å建çdba
create user dba for login dba with default_schema=dbo
并æå®æ°æ®åºç¨æ·âdbaâ çé»è®¤ schema æ¯âdboâãè¿æå³ç ç¨æ·âdbaâ å¨æ§è¡âselect * from tâï¼å®é
ä¸æ§è¡çæ¯ âselect * from dbo.tâã
3. éè¿å å
¥æ°æ®åºè§è²ï¼èµäºæ°æ®åºç¨æ·âdbaâæéï¼
--éè¿å å
¥æ°æ®åºè§è²ï¼èµäºæ°æ®åºç¨æ·âdb_ownerâæé
exec sp_addrolemember 'db_owner', 'dba'
æ¤æ¶ï¼dba å°±å¯ä»¥å
¨æ管çæ°æ®åº mydb ä¸ç对象äºã
å¦ææ³è®© SQL Server ç»éå¸æ·âdbaâ访é®å¤ä¸ªæ°æ®åºï¼æ¯å¦ mydb2ã å¯ä»¥è®© sa æ§è¡ä¸é¢çè¯å¥ï¼
--让 SQL Server ç»éå¸æ·âdbaâ访é®å¤ä¸ªæ°æ®åº
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go
æ¤æ¶ï¼dba å°±å¯ä»¥æ两个æ°æ®åº mydb, mydb2 ç管çæéäºï¼