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µÄÐ¶ÔØÊÇÒ»¸ö·Ç³£Í·ÌÛµÄÎÊÌâ¡£ÎÒÔø¾³¢ÊÔ¹ýÖ±½ÓʹÓá¾Ìí¼Ó»òɾ³ý³ÌÐò¡¿¹¤¾ßÐ¶ÔØ¡¢Çå³ý°²×°Ä¿Â¼¡¢É¾³ý×¢²á±íÄÚÈݵȵȸ÷ÖÖ·½Ê½×ÛºÏÐ¶ÔØ£¬ÃãÇ¿³É¹¦¡£ÏÖÔÚÖÕÓÚÕÒµ½ÁËÒ»¸öʰ빦±¶µÄ·½·¨£¬¶à´Î³¢ÊÔ£¬Î´ÓÐʧ°Ü,¾ßÌåÈçÏÂ:
1.ÏÂÔØÐ¶ÔØ¹¤¾ß£¬ÓÐÁ½ÖÖ£º
µÚÒ»ÖÖÊÇ΢Èí¹Ù·½ÌṩµÄ¹¤¾ß(msicuu2.exe)
http://support.mic ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......
ÓõÚÒ»ÖÖ·½·¨£¬ÉÏ´«·þÎñÆ÷ºó£¬ÔÚһЩ·þÎñÆ÷ÖУ¬¿ÉÄÜ»á³öÏÖÒÔÏ´íÎó£º
±ØÐëÔÚweb.configÖÐ¼Ó <system.web><identity impersonate="false"/></system.web> ,·ñÔò³öÏÖ´íÎó£ºSystem.Data.OleDb.OleDbException: δָ¶¨µÄ´íÎó¡£¼ÓÁËÒԺ󣬵ã¶à¼¸´ÎÁ´½Ó£¬» ......
declare @p int
declare @p1 int
declare @count int
set @p=0
set @p1=10
set @count=2
if(@count<>0 or @count<>1)
set @p=@p1*@count-10
SELECT [t1].[userid], [t1].[username], [t1].[userorder]
from (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[userorder]) AS [ROW_NU ......