SQL SERVERµÇ¼Óû§ÔÚ·þÎñÆ÷ÉϵÄȨÏÞ
SQL SERVERµÇ¼Óû§ÔÚ·þÎñÆ÷ÉϵÄȨÏÞ
Ò»¸öµÇ¼Óû§µ½µ×ÔÚSQL SERVERʵÀýÉÏÓÐʲôÑùµÄȨÏÞ£¬ÏÂÃæÒÔSQL SERVER2005ΪÀýÀ´Ï¸Êýһϡ£
Ò»£®
Ê×ÏȲ鿴¸ÃµÇ¼Óû§ÊôÓÚÄĸö¹Ì¶¨·þÎñÆ÷½ÇÉ«¡£ËùÓÐSQL SERVERµÄµÇ¼Óû§ºÍ½ÇÉ«¶¼»áÔÚmaster.sys.server_principalÊÓͼÉÏÓÐÒ»Ìõ¼Ç¼¡£¶ø¼Ç¼µÇ¼Óû§ÊôÓÚʲô·þÎñÆ÷½ÇÉ«µÄÊÓͼÊÇmaster.sys.server_role_members¡£
¶þ£®
²é¿´µÇ¼Óû§ÔÚ·þÎñÆ÷¼¶±ðÉÏÓÐʲôȨÏÞ£¬¸ÃȨÏ޼ǼÓÚÊÓͼsys.server_permissionsÖС£
Èý£®
²é¿´µÇ¼Óû§ÔÚ·þÎñÆ÷¼¶±ðÉÏÓÐʲôËùÓÐȨ¡£×¢ÒⲻͬÓÚÉÏÃæµÄȨÏÞ£¬ÒòΪSQL SERVERÊDz»ÄÜÏò°²È«¶ÔÏóµÄËùÓÐÕß·ÖÅäȨÏ޵ģ¬ËùÒÔȨÏÞºÍËùÓÐȨÊÇ·Ö¿ªµÄ¡£ËùÓÐȨ¼ûÏÂͼ¡£
ËÄ£®
¼ì²éËùÓеÄÊý¾Ý¿â£¬¿´ÊÇ·ñÓÐÊý¾Ý¿âÓû§Ó³ÉäÖÁ¸ÃµÇ¼Ãû¡£SQL SERVER2005ÖÐͬһÊý¾Ý¿âÖеIJ»Í¬Óû§²»ÄÜÓ³ÉäÖÁͬһµÇ¼Ãû¡£Ó³Éä¹Øϵ¼Ç¼ÓÚÊÓͼsys.database_principalsÖС£
ÈôûÓÐÓû§Ó³É䣬Ôò¼ì²é´ËÊý¾Ý¿âÖÐguestÓû§ÊÇ·ñ¼¤»î¡£Èô¼¤»îÔòÔÚ´ËÊý¾Ý¿âÖÐÓµÓÐguestµÄȨÏÞ£»ÈôûÓм¤»î£¬ÔòÔÚ´ËÊý¿âÖÐûÓÐȨÏÞ¡£
ÈôÓÐÓû§Ó³É䣬Ôò¸ÃÊý¾Ý¿âÓû§µÄȨÏÞ¾ÍÊǵǼÓû§ÔÚ¸ÃÊý¾Ý¿âÖеÄȨÏÞ¡£
Î壮
ÉÏÃæµÄÊý¾Ý¿âÓû§£¨¿ÉÄܾÍÊÇguestÓû§£©£¬ËüÃǵÄȨÏÞµ½µ×ÊÇʲôÄØ£¿
Ê×ÏÈÒª¼ì²é¸ÃÊý¾Ý¿âÓû§ÊÇ·ñÊôÓÚÊý¾Ý¿â½ÇÉ«£¨¹Ì¶¨½ÇÉ«¡¢×Ô¶¨Òå½ÇÉ«ºÍÓ¦ÓóÌÐò½ÇÉ«£©£¬ËùÓеÄÓû§ºÍ½ÇÉ«¶¼¼Ç¼ÓÚÏàÓ¦Êý¾Ý¿âµÄsys.databse_principalsÊÓͼÖС£¶ø¼Ç¼Óû§ÊôÓÚʲô½ÇÉ«µÄÊÓͼÊÇsys.database_role_member¡£
ÈôÓû§ÊôÓÚij¸ö½ÇÉ«£¬ÔòÓû§¾ÍÓµÓиýÇÉ«µÄȨÏÞ¡£Ò»¸öÓû§¿ÉÒÔͬʱÊôÓÚ¶à¸ö½ÇÉ«£¬¶øÇÒÒ»¸ö½ÇÉ«¿ÉÒÔÊôÓÚÁíÒ»¸ö½ÇÉ«¡£ÏÂÃæµÄ´úÂëÑÝʾÁËÔÚSQL SERVER 2005ÖÐÈçºÎÏÔʾһ¸öÓû§£¨user1£©ËùÊôµÄËùÓнÇÉ«µÄÃû³Æ£º
;with members as
( select *
from sys.database_role_members
where user_name(member_principal_id) = 'user1'
union all
select b.*
from members a
join sys.database_role_members b
on b.member_principal_id = a.role_principal_id
)
select user_name(role_principal_id)
from members
ÒòΪËùÓеÄÊý¾Ý¿âÓû§±ØÈ»ÊôÓÚpublic½ÇÉ«£¬Òò´ËËùÓÐÓû§±ØȻӵÓÐPublic½ÇÉ«µÄȨÏÞ¡£
ÖªµÀÁËËùÊôµÄ½ÇÉ«£¬ÏÂÃæµÄÎÊÌâ¾Í¼òµ¥ÁË¡£½ÇÉ«ÒÔ¼°Êý¾Ý¿âÓû§µÄȨÏ޼ǼÓÚÊÓͼsys.databaser_permissionsÖС£
Áù£®
ÎÒÃÇ»¹ÐèÒª²é¿´Óû§ºÍ½ÇÉ«ÔÚÊý¾Ý¿â¼¶±ðÉÏÓÐʲôËùÓÐȨ¡£ËùÓÐȨ¼ûÏÂͼ¡£
Æߣ®
ÈôÔÚÆäËûÖ÷ÌåÉÏÓÐImpersonateµÄȨÏÞ£¬Ôò±íÊ
Ïà¹ØÎĵµ£º
תÔØ˵Ã÷£º¸øÕýÔÚ×öϵͳºÍÏë×öϵͳµÄÈË£¬SQL²©´ó¾«ÉÄãÃÇ»áÓõ½µÄ¡£——mAysWINd
Ò»¸öÌâÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµ ......
ÈçºÎÓÃSQLÓïÑÔÑ¡Ôñ±íÖеĵڶþÌõµÚÈýÌõµÚNÌõ¼Ç¼
--IDΪΨһÐÔ¾ÍÐÐÁË
select top 1 * from table
where ID not in(select top 1 ID from table)--µÚ2Ìõ
select top 1 * from table
where ID not in(select top 2 ID from table)--µÚ3Ìõ
......
¹ØÓÚSQL Prompt
µÄÆƽâÎÄÕÂÔ°×ÓÀïÒѾÓв»ÉÙÈË·¢¹ýÁË¡£Æƽâ˼·ÎÒÒ²²»ÏëÖظ´£¬²»ÖªµÀµÄ¿ÉÒԲο¼£ºhttp://www.cnblogs.com/jintan/archive/2008/06/16/1223282.html
¹Ù·½ÏÂÔصØÖ·ÊÇ£ºhttp://downloads.red-gate.com/SQLToolbelt.exe
½ÓÏÂÀ´ÎÒ¾Í˵˵¾ßÌåÔõô×ö°É¡£ÎÒ¾Í˵˵×î¼òµ¥×îɵ¹ÏµÄ×ö·¨£¬²»ÐèÒª¶®ILÕÕÑùÆƽ⣡
× ......
Ò»£¬
bcpÃüÁîÏê½â
bcpÃüÁîÊÇSQL ServerÌṩµÄÒ»¸ö¿ì½ÝµÄÊý¾Ýµ¼Èëµ¼³ö¹¤¾ß¡£Ê¹ÓÃËü²»ÐèÒªÆô¶¯ÈκÎͼÐιÜÀí¹¤¾ß¾ÍÄÜÒÔ¸ßЧµÄ·½Ê½µ¼Èëµ¼³öÊý¾Ý¡£bcpÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB-LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£bcp¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö ......
(1) Connect to the Analysis server, select the database which we want it to be automatically processed. Right click on this database, choose ‘Process’:
(2) In the opening ‘Process database’ form, click the ‘Script Action ......