ASP new account in SQL SER2K and the implementation of account permissions
Posted in: 2002-5-12
' The following is a SQL server2000 user function added to the ASP, and to create a database, give him the dbo permissions
' **************** Note: SQL Server is not authenticated in a way that is Windows only,
' **************** allows remote SQL Server connections
' **************** The function has passed the test ****************************
' What's the problem? Welcome to communicate with me, and later will introduce some management procedures for SQL Server
' Parameters: Strloginname: New login name, strpwd: password for login name, strDbName: New database name
' Function local variable description: strserver: Server's machine name (local locally), Struid:sql Administrator,
' Strsapwd:sql admin password. The above three variables should be set according to your situation
' This function primarily invokes the system stored procedure to implement the
' Note: This function does not have fault-tolerant processing, if there is an error, you can determine that there is a problem with your SQL Server settings, or that the login account or the database exists
' Call Addusertomssql ("Testlogin", "Iamhere", "Db_test")
Sub Addusertomssql (Strloginname,strpwd,strdbname)
' Define server variables and system administrator login information, modify as appropriate
Dim strserver,struid,strsapwd
Strserver= "(local)"
struid= "SA"
Strsapwd= ""
Dim Conn ' Database connection
Dim strdsn ' Database connection string
Dim strcmd ' command string
strdsn= "Driver={sql server};server=" &StrServer& "uid=" &StrUid& ";p wd=" &StrSaPwd& "; Database=master "
' Establish a connection with the database master
Set Conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open strDSN
' Create a new database
strcmd= "CREATE DATABASE" &strdbname
Conn.execute (Strcmd)
' Create a new login account
strcmd= "sp_addlogin '" &StrLoginName& "', ' &StrPwd&" ', ' "&StrDBName&" "
Conn.execute (Strcmd)
Conn.close
' Establish a connection to the new database and assign it to the new login account right to access the new database
strdsn= "Driver={sql Server}; Server= "&StrServer&" uid= "&StrUid&";p wd= "&StSarPwd&";d atabase= "&strdbname
strcmd= "sp_grantdbaccess '" &StrLoginName& ""
Conn.Open strDSN
Conn.execute (Strcmd)
' Make the new login account the owner of the new database
strcmd= "sp_addrolemember ' db_owner ', '" &StrLoginName& ""
Conn.execute (Strcmd)
' Turn off the release connection
Conn.close
Set conn=nothing
Response.Write "User" &StrLoginName& "successfully established!" and has set up a database for him "&StrDBName&"!
End Sub
