' The following is to add a SQL Server2000 user function to Asp, and to create a database, give it permissions to dbo ' ***************************Note: The verification method of SQL Server should not be only Windows, ' *************************** Allow remote SQL Server connections ' ************************ This function has passed the test************************************************ ' If you have any questions, please feel free to communicate with me. In the future, some management operation programs for SQL Server 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 procedures to implement
' Note: This function does not have fault tolerance. If an error occurs, you can be sure that there is a problem with your SQL server setup, or that the login account or the database already exists. ' call AddUserToMSSQL("testlogin","iamhere","db_test")
Sub AddUserToMSSQL(StrLoginName,StrPwd,StrDBName)'Define server variables and system administrator login information, and modify it according to the specific situation
Dim StrServer,StrUid,StrSaPwd StrServer="(local)" StrU StrSaPwd="" Dim Conn 'Database Connection
Dim StrDSN 'Database connection string
Dim StrCmd 'Command String
StrDSN="driver={SQL server};server="&StrServer&";u;pwd="&StrSaPwd&";database=master" 'Create a connection to the database master set Conn = ("") StrDSN
'Create a new database StrCmd="CREATE DATABASE"&StrDBName (StrCmd) 'Create a new login account StrCmd="sp_addlogin '"&StrLoginName&"','"&StrPwd&"','"&StrDBName&"'" (StrCmd)
'Establish a connection to the newly created database and assign the new login account the right to access the newly created database StrDSN="driver={SQL server}; server="&StrServer&";u;
pwd="&StSarPwd&";database="&StrDBName StrCmd="sp_grantdbaccess '"&StrLoginName&"'" StrDSN (StrCmd)
'Make the new login account the owner of the newly created database StrCmd="sp_addrolemember 'db_owner','"&StrLoginName&"'" (StrCmd) 'Close the release connection Set Conn=Nothing "User"&StrLoginName&" Successfully established!, and a database has been established for him "&StrDBName&"!" End Sub