'The following is to add a SQLServer2000 user function to Asp, and to create a database, give it permissions to dbo'************************************Note: The verification method of SQLServer should not be selected as Windows only, '************************************ This function has passed the test******************************************************'If you have any questions, please communicate with me. In the future, some management operation programs for SQLServer will be launched.
'Parameters: StrLoginName: Added login name, StrPwd: login name password, StrDBName: Create new database name' Description of local variables in the function: StrServer: The machine name where the server is located (local) StrUid: sql administrator, 'StrSaPwd: sql administrator password. The above three variables should be set according to your situation
'This function mainly calls the system stored procedure to implement it
'Note: This function does not have fault tolerance. If an error occurs, you can be sure that there is a problem with your SQLserver setup, or that the login account or the database already exists.'callAddUserToMSSQL("testlogin","iamhere","db_test")
SubAddUserToMSSQL(StrLoginName,StrPwd,StrDBName)'Define server variables and system administrator login information, modify it according to the specific situation
DimStrServer,StrUid,StrSaPwdStrServer="(local)"StrUid="sa"StrSaPwd=""DimConn' database connection
DimStrDSN' database connection string
DimStrCmd' command string
StrDSN="driver={SQLserver};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database=master"'Create a connection to the database master setConn=Server.CreateObject("ADODB.Connection")Conn.OpenStrDSN
'Create a new database StrCmd="CREATEDATABASE"&StrDBNameConn.execute(StrCmd)'Create a new login account StrCmd="sp_addlogin'"&StrLoginName&"','"&StrPwd&"','"&StrDBName&"'"Conn.execute(StrCmd)Conn.Close
'Create a connection to the new database and assign the new login account to access the new database StrDSN="driver={SQLserver};server="&StrServer&";uid="&StrUid&";
pwd="&SarPwd&";database="&StrDBNameStrCmd="sp_grantdbaccess'"&StrLoginName&"'"Conn.OpenStrDSNConn.execute(StrCmd)
'Make the new login account the owner of the new database StrCmd="sp_addrolemember'db_owner','"&StrLoginName&"'"Conn.execute(StrCmd)' Close release connection Conn.CloseSetConn=NothingResponse.Write"User"&StrLoginName&"Successfully established!, and a database "&StrDBName&" has been established for him"EndSub