ms sql server ´´½¨Óû§
SQL code
´´½¨½ÇÉ«£¬Óû§£¬È¨ÏÞ
/*--ʾÀý˵Ã÷
ʾÀýÔÚÊý¾Ý¿âpubsÖд´½¨Ò»¸öÓµÓбíjobsµÄËùÓÐȨÏÞ¡¢ÓµÓбítitlesµÄSELECTȨÏ޵ĽÇÉ«r_test
Ëæºó´´½¨ÁËÒ»¸öµÇ¼l_test£¬È»ºóÔÚÊý¾Ý¿âpubsÖÐΪµÇ¼l_test´´½¨ÁËÓû§ÕË»§u_test
ͬʱ½«Óû§ÕË»§u_testÌí¼Óµ½½ÇÉ«r_testÖУ¬Ê¹Æäͨ¹ýȨÏ޼̳лñÈ¡ÁËÓë½ÇÉ«r_testÒ»ÑùµÄȨÏÞ
×îºóʹÓÃDENYÓï¾ä¾Ü¾øÁËÓû§ÕË»§u_test¶Ô±ítitlesµÄSELECTȨÏÞ¡£
¾¹ýÕâÑùµÄ´¦Àí£¬Ê¹ÓÃl_testµÇ¼SQL ServerʵÀýºó£¬ËüÖ»¾ßÓбíjobsµÄËùÓÐȨÏÞ¡£
--*/
USE pubs
--´´½¨½ÇÉ« r_test
EXEC sp_addrole 'r_test'
--ÊÚÓè r_test ¶Ô jobs ±íµÄËùÓÐȨÏÞ
GRANT ALL ON jobs TO r_test
--ÊÚÓè½ÇÉ« r_test ¶Ô titles ±íµÄ SELECT ȨÏÞ
GRANT SELECT ON titles TO r_test
--Ìí¼ÓµÇ¼ l_test,ÉèÖÃÃÜÂëΪpwd,ĬÈÏÊý¾Ý¿âΪpubs
EXEC sp_addlogin 'l_test','pwd','pubs'
--ΪµÇ¼ l_test ÔÚÊý¾Ý¿â pubs ÖÐÌí¼Ó°²È«ÕË»§ u_test
EXEC sp_grantdbaccess 'l_test','u_test'
--Ìí¼Ó u_test Ϊ½ÇÉ« r_test µÄ³ÉÔ±
EXEC sp_addrolemember 'r_test','u_test'
--¾Ü¾ø°²È«ÕË»§ u_test ¶Ô titles ±íµÄ SELECT ȨÏÞ
DENY SELECT ON titles TO u_test
/*--Íê³ÉÉÏÊö²½Öèºó,Óà l_test µÇ¼,¿ÉÒÔ¶Ôjobs±í½øÐÐËùÓвÙ×÷,µ«ÎÞ·¨¶Ôtitles±í²éѯ,ËäÈ»½ÇÉ« r_test ÓÐtitles±íµÄselectȨÏÞ,µ«ÒѾÔÚ°²È«ÕË»§ÖÐÃ÷È·¾Ü¾øÁ˶ÔtitlesµÄselectȨÏÞ,ËùÒÔl_testÎÞtitles±íµÄselectȨÏÞ--*/
--´ÓÊý¾Ý¿â pubs ÖÐɾ³ý°²È«ÕË»§
EXEC sp_revokedbaccess 'u_test'
--ɾ³ýµÇ¼ l_test
EXEC sp_droplogin 'l_test'
--ɾ³ý½ÇÉ« r_test
EXEC sp_droprole 'r_test'
SQL code
--1. ´´½¨Ê¾Àý»·¾³¡£
Ê×ÏÈʹÓÃÏÂÃæµÄ´úÂë´´½¨Ò»¸öµÇ¼l_test£¬²¢ÇÒΪµÇ¼ÔÚÊý¾Ý¿âpubsÖд´½¨¹ØÁªµÄÓû§ÕË»§u_test£¬²¢ÇÒÊÚÓèÓû§ÕË»§u_test¶Ô±ítitlesµÄSELECTȨÏÞ£¬ÓÃÒÔʵÏֵǼl_testÁ¬½Óµ½SQL ServerʵÀýºó£¬¿ÉÒÔ·ÃÎʱítitles¡£È»ºó´´½¨ÁËÒ»¸öÓ¦ÓóÌÐò½ÇÉ«r_p_test£¬ÊÚÓè¸Ã½ÇÉ«¶Ô±íjobsµÄSELECTȨÏÞ£¬ÓÃÒÔʵÏÖ¼¤»îr_p_testʱ£¬ÔÊÐí·ÃÎÊÌض¨µÄ±íjobs¡£
USE pubs
--´´½¨Ò»¸öµÇ¼ l_test, ÃÜÂë pwd, ĬÈÏÊý¾Ý¿â pubs
EXEC sp_addlogin 'l_test','pwd','pubs'
--ΪµÇ¼ l_test ÔÚÊý¾Ý¿â pubs ÖÐÌí¼Ó°²È«ÕË»§ u_test
EXEC sp_grantdbaccess 'l_test','u_test'
--ÊÚÓ谲ȫÕË»§ u_test ¶Ô titles ±íµÄ SELECT ȨÏÞ
GRANT SELECT ON titles TO u_test
--
Ïà¹ØÎĵµ£º
SQL Server 2005
±¾Æª½éÉÜÈçºÎÁ´½Óµ½Sql Server 2005.
²ÉÓÃSQL ±¾µØ¿Í»§ ODBCÇý¶¯
²ÉÓÃSqlÉí·ÝÑéÖ¤
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
ÄãÕýÔÚʹÓÃSQL Server 2005 Express£¿±ðÍü¼ÇÁË£¬ServernameµÄÓï·¨ÊÇServername\SQLEXPRESS£¬Äã¿ÉÒÔÓÃÄã°²× ......
ÓõÚÒ»ÖÖ·½·¨£¬ÉÏ´«·þÎñÆ÷ºó£¬ÔÚһЩ·þÎñÆ÷ÖУ¬¿ÉÄÜ»á³öÏÖÒÔÏ´íÎó£º
±ØÐëÔÚweb.configÖÐ¼Ó <system.web><identity impersonate="false"/></system.web> ,·ñÔò³öÏÖ´íÎó£ºSystem.Data.OleDb.OleDbException: δָ¶¨µÄ´íÎó¡£¼ÓÁËÒԺ󣬵ã¶à¼¸´ÎÁ´½Ó£¬» ......
¹¤¾ß£º
Sybase PowerDesigner 12.5
Microsoft SQL Server 2005
µÚÒ»²½£º¸ÅÒªÉè¼Æ
´ò¿ªPowerDesignerÈí¼þ£¬Éè¼Æ“¸ÅÄîÊý¾ÝÄ£ÐÍ”(Conceptual Data Model)£º
ͼ1
Éè¼Æ±í½á¹¹£º
ͼ2
ÔÚÉè¼ÆÊôÐÔ(×Ö¶Î)µÄʱºò£¬Èý¸ö×Öĸ(M¡¢P¡¢D)·Ö±ð±íʾ£º
M£ºÊÇ·ñΪ¿Õ;(√±íʾ²»ÔÊÐíΪ¿Õ)
P£ºÊÇ·ñ뻅 ......
DATENAME([Year],
dbo.reservation.begintime) + N'Äê' + CAST(DATEPART([Month],
dbo.reservation.begintime) AS varchar) + N'ÔÂ' + DATENAME([Day],
dbo.reservation.begintime) + N'ÈÕ' + DATENAME([Hour], dbo.r ......