ASP new account in SQL SER2K and the implementation of account permissions

Source: Internet
Author: User
Tags variables create database
' 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



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.