Complete the function of establishing a device in the SQL Server database, establishing a database, creating a table, and allocating permissions, making it easy to build the SQL Server environment you need of the database application, simply use the SQL Enterprise Manager configuration database.
This gadget is developed by VB, using ADO to access the database, implement changes to the database structure, and other languages can also be used as a reference. Start VB6.0, create new projects, in the menu - Engine - reference "Microsoft ActiveX Data Objects 2.0 Library, the code needs to be
DIM CONN As New Adodb.connection
Define ADO database objects
Conn.connectionstring = "driver = {SQL Server};" & _
"Server =" & Servername & "; UID =" & UserName & ";
PWD = "& password"; database = "& databasene"
Connection data string
Conn.open connection database
Note: ServerName is the server name; username is a username; Password is a user password; DatabaseName To log in the database name, you can be empty.
The core code is as follows:
First, establish a database
Principle: Establish a database first to initialize a database device and establish a database on this device. All device names have records in the system table "sysdevices", all database names are recorded in the system table "sysdatabases". Before being established, it is best to query these two system tables first, see if the name already exists. A physical name and idle device identification number required before establishing a device.
Initialization equipment syntax:
Disk init name = "device_name", phyname =
"physical_name", VDEVNO = Device_Number,
Size = Numberofblock
Description: Here, Name is a database device name (a valid identifier), the phyname (physical name of the database device) is the original disk partition UNIX or peripheral (VMS) name, or the file name of the operating system. When VDEVNO, the device identification number, the legal value is 1-255, and the unit of size is 2KB block, such as 1MB (1024KB), the size value is 512.
Creating database syntax: CREATE DATABASE DATABASE_NAME [on Database_Device]
Description: Database_name is the database name to be built, Database_Device is a device name
To create a new database, you need a device name, database name, physical name, and device number. Specific steps are as follows:
We assume that the user wants to set up the device DBNAME and establish a database DBNAME on the device DBNAME.
1) Get the device name BNAME is the device name given by the user; first query system table sysdevices, see if the device name DBNAME given by the user has existed, if this device name exists, you need to replace a device name, because the device name is unique of.
SQL = "SELECT * from sysdeviceswhere name =" & dbname & "
SET RS = Conn.execute (SQL)
IF not r.
Msgbox "Equipment Name" & Dbname & "
existed! ", 16," Please re-enter the name "
EXIT SUB
END IF
2) Get the database name. DBNAME is the database name given by the user; query system table sysdatabases, see if the database name DBNAME given by the user already exists, if this data is inventory, you need to replace a database name, like device name, the database name is also unique
SQL = "SELECT * from Sysdatabases
Where name = "& dbname &"
SET RS = conn.execute (SQL) below the code
3) Get the Phyname physical name. Query the physical location serverPath of the database file on the server, typically, we can query Master (this is the SQL Server's primary library name) database from the system table sysdevices, such as g: /msql/data/master.dat, then us The database can be built in the "G: / MSSQL / DATA /" directory.
SQL = "SELECT NAME, PHYNAME from sysdevices"
LOW / 16777216 is a device number
SET RS = Conn.execute (SQL)
Then traverse the record object RS, when Name = "Master", remove the phyname,
Thereby, physical location serverPath = g: / mssql / data /.
4) Get an idle device number vdevno. The legal value of the device number is 1 ~ 255, traversed these numbers, find out unused free device numbers, the following procedure gets the existing device number
SQL = "SELECT DISTINCT LOW / 16777216
From sysdevices Order by low / 16777216
LOW / 16777216 is a device number
5) Establish a database. The information required is ready, and the database can be established (Note: "" & chr (34) & "is a" "" "double quotation mark, so after processing, the grammar requirements; the database is 20m, then DBSIZE = 512 * 20)
SQL = "Disk init name =" & chr (34) & ""
& dbname & "& chr (34) &", physname = "
& Chr (34) & "& ServerPath &" & dbname
& ".dat" & chr (34) & ", vdevno =" & vdevno
& "Size =" & Dbsize & "
SET RS = conn.execute (SQL) initializer
SQL = "Create Database" & DBName & "& dbname &" = "& dbsize &" "
Note:
The first DBNAME is the database name,
The second DBNAME is the device name
SET RS = Conn.execute (SQL)
Create a database DBNAME on the device DBNAME
Msgbox "Database" & Dbname & "" built on the server
"& ServerPath &" & Dbname & ".dat",
Established success! ", 64," success "
Second, establish a form
The establishment of a table is relatively simple, here is used here, the type of field and the default value field, the syntax is as follows:
Create Table Table_Name
(Field_name Data_Type [not null | null], ...)
Description: Table_name is a new table name, Field_name as a field name, DATA_TYPE is a data type.
(Note the following FileID IntTentity field automatically counts,
DateTime NOT NULL Default (Getdate ()) field
The library has a default value, generated by the database at the time).
SQL = "Create Table" & Tablename & "
(FileID Int IDENTITY, FILETIME dateTime Not
Null Default (getdate ()), FileImage Image Null
Conn.execute SQL creation form
Third, establish a user group user
Creating a user group and user cannot complete directly through the SQL statement, requiring SQL Server store procedure sp_addlogin, sp_addgroup, sp_adduser. We assume that the new login account is username1, the username is username1, the group name is group1, then the steps are as follows:
1) Establish a user's login account
Syntax: sp_addlogin login_name, password [, defdb]
Where login_name is the user's login name, password is used
The password of the household, the default database name login on DEFDB. Establish a number
According to the login account of DatabaseName:
SQL = "EXECUTE SP_ADDLOGIN" & UserName1 & ","
& Password1 & "," & DatabaseName & "
SET RS = Conn.execute (SQL)
2) Increase user groups
Syntax: sp_addgroup group_name
Among them, group_name is a new group name.
SQL = "EXECUTE SP_ADDGROUP" & GROUP1 & "
SET RS = Conn.execute (SQL)
3) Increase users
Syntax: sp_adduser login_name
[, name_in_db] [, grpname]]
Where, login_name username, name_in_db is the name of the user in the current database (here is the first login account established)
Username1), Grpname is the group name of the group to join the user.
Add user username1 in the database DatabaseName:
SQL = "Execute sp_adduser" & username1 & "," & us)
UserName1 & "," & Group1 & "
Note: The first username1 is the username, the second username1 is
Database DatabaseName login account
SET RS = Conn.execute (SQL)
Fourth, allocation rights
Grammar: Grant Permission_list on object_name to who
Among them, Permission_List is the right list to be assigned, Object_name is permission on this object, and WHO is a licensed user.
First get all users to create a table name (Type = U) from the system table sysobjects
SQL = "SELECT NAME from Sysobjects Where Type = U"
SET RS = Conn.execute (SQL)
The desired table is then selected to assign the right to other users. For example, hereby assigning the read permissions of TABLENAME3 to group group1.
SQL = "Grant SELECT ON" & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME3 & TABLENAME
"To" & Group1 & "
Conn.execute SQL
Due to this gadget, the SQL Server database configuration is simple and convenient.
