A
AbanoubZak
Guest
Hello,
I have an application that create DB then Tables inside Then some login Users
Public Function CreateDB()
MessageBox.Show("CreateDB")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
USE [master]
/****** Object: Database [AZFTS] Script Date: 02/15/2020 00:15:14 ******/
CREATE DATABASE [AZFTS] ON PRIMARY
( NAME = N'AZFTS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AZFTS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS_log.ldf' , SIZE = 15040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [AZFTS] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AZFTSACC].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AZFTS] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [AZFTS] SET ANSI_NULLS OFF
ALTER DATABASE [AZFTS] SET ANSI_PADDING OFF
ALTER DATABASE [AZFTS] SET ANSI_WARNINGS OFF
ALTER DATABASE [AZFTS] SET ARITHABORT OFF
ALTER DATABASE [AZFTS] SET AUTO_CLOSE ON
ALTER DATABASE [AZFTS] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [AZFTS] SET AUTO_SHRINK OFF
ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [AZFTS] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [AZFTS] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [AZFTS] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [AZFTS] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [AZFTS] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [AZFTS] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [AZFTS] SET DISABLE_BROKER
ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [AZFTS] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [AZFTS] SET TRUSTWORTHY OFF
ALTER DATABASE [AZFTS] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [AZFTS] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [AZFTS] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [AZFTS] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [AZFTS] SET READ_WRITE
ALTER DATABASE [AZFTS] SET RECOVERY SIMPLE
ALTER DATABASE [AZFTS] SET MULTI_USER
ALTER DATABASE [AZFTS] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [AZFTS] SET DB_CHAINING OFF
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=master;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
Public Function CreateDBT()
MessageBox.Show("CreateDBT")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
USE [AZFTS]
/****** Object: Table [dbo].[ShiftStates] Script Date: 02/14/2020 14:31:56 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[ShiftStates](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EGPOpen] [money] NULL,
[USDOpen] [money] NULL,
[EUROpen] [money] NULL,
[GBPOpen] [money] NULL,
[EGPClose] [money] NULL,
[USDClose] [money] NULL,
[EURClose] [money] NULL,
[GBPClose] [money] NULL,
[UserName] [varchar](max) NULL,
[MachineName] [varchar](max) NULL,
[DateOn] [date] NULL,
[TimeOn] [time](7) NULL,
[DateOff] [date] NULL,
[TimeOff] [time](7) NULL,
[State] [varchar](max) NULL,
[EGP] AS ([egpclose]-[egpopen]),
[USD] AS ([USDClose]-[USDOpen]),
[EUR] AS ([EURClose]-[EUROpen]),
[GBP] AS ([GBPClose]-[GBPOpen]),
CONSTRAINT [PK_ShiftStates] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
but the Login user give me Error "error 26 - error locating server/instance specified" and i use the same code and conection string except for the DB i change this time from Master to "AZFTS" as it's my DB that will use to create login user in
Public Function adduser2()
MessageBox.Show("adduser2")
Dim username As String = Environment.UserName.ToString
Dim Domain As String = Environment.MachineName.ToString
Dim domainuser As String = "" & Domain & "\" & username & ""
Dim databaseName As String = "AZFTS"
Dim login As String = "AZFTSUSER"
Dim password As String = "123456789"
Dim passwordsa As String = "123456789123"
Dim script =
<SQL>
CREATE LOGIN [<%= login %>] WITH PASSWORD=N'<%= password %>', DEFAULT_DATABASE=[<%= databaseName %>]
ALTER LOGIN [<%= domainuser %>] DISABLE
EXEC sp_password NULL, '<%= passwordsa %>', 'sa'
USE [<%= databaseName %>]
CREATE USER [<%= login %>] FOR LOGIN [<%= login %>]
ALTER USER [<%= login %>] WITH DEFAULT_SCHEMA=[dbo]
EXECUTE sp_addrolemember db_datareader, 'AZFTSUSER'
EXECUTE sp_addrolemember db_datawriter, 'AZFTSUSER'
EXECUTE sp_addrolemember db_backupoperator, 'AZFTSUSER'
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
Any one have idea why first two have no problem but stop with number 3 which is "adduser2"
Continue reading...
I have an application that create DB then Tables inside Then some login Users
Public Function CreateDB()
MessageBox.Show("CreateDB")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
USE [master]
/****** Object: Database [AZFTS] Script Date: 02/15/2020 00:15:14 ******/
CREATE DATABASE [AZFTS] ON PRIMARY
( NAME = N'AZFTS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AZFTS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS_log.ldf' , SIZE = 15040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [AZFTS] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AZFTSACC].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AZFTS] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [AZFTS] SET ANSI_NULLS OFF
ALTER DATABASE [AZFTS] SET ANSI_PADDING OFF
ALTER DATABASE [AZFTS] SET ANSI_WARNINGS OFF
ALTER DATABASE [AZFTS] SET ARITHABORT OFF
ALTER DATABASE [AZFTS] SET AUTO_CLOSE ON
ALTER DATABASE [AZFTS] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [AZFTS] SET AUTO_SHRINK OFF
ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [AZFTS] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [AZFTS] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [AZFTS] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [AZFTS] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [AZFTS] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [AZFTS] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [AZFTS] SET DISABLE_BROKER
ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [AZFTS] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [AZFTS] SET TRUSTWORTHY OFF
ALTER DATABASE [AZFTS] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [AZFTS] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [AZFTS] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [AZFTS] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [AZFTS] SET READ_WRITE
ALTER DATABASE [AZFTS] SET RECOVERY SIMPLE
ALTER DATABASE [AZFTS] SET MULTI_USER
ALTER DATABASE [AZFTS] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [AZFTS] SET DB_CHAINING OFF
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=master;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
Public Function CreateDBT()
MessageBox.Show("CreateDBT")
Dim Domain As String = Environment.MachineName.ToString
Dim script =
<SQL>
USE [AZFTS]
/****** Object: Table [dbo].[ShiftStates] Script Date: 02/14/2020 14:31:56 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[ShiftStates](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EGPOpen] [money] NULL,
[USDOpen] [money] NULL,
[EUROpen] [money] NULL,
[GBPOpen] [money] NULL,
[EGPClose] [money] NULL,
[USDClose] [money] NULL,
[EURClose] [money] NULL,
[GBPClose] [money] NULL,
[UserName] [varchar](max) NULL,
[MachineName] [varchar](max) NULL,
[DateOn] [date] NULL,
[TimeOn] [time](7) NULL,
[DateOff] [date] NULL,
[TimeOff] [time](7) NULL,
[State] [varchar](max) NULL,
[EGP] AS ([egpclose]-[egpopen]),
[USD] AS ([USDClose]-[USDOpen]),
[EUR] AS ([EURClose]-[EUROpen]),
[GBP] AS ([GBPClose]-[GBPOpen]),
CONSTRAINT [PK_ShiftStates] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
but the Login user give me Error "error 26 - error locating server/instance specified" and i use the same code and conection string except for the DB i change this time from Master to "AZFTS" as it's my DB that will use to create login user in
Public Function adduser2()
MessageBox.Show("adduser2")
Dim username As String = Environment.UserName.ToString
Dim Domain As String = Environment.MachineName.ToString
Dim domainuser As String = "" & Domain & "\" & username & ""
Dim databaseName As String = "AZFTS"
Dim login As String = "AZFTSUSER"
Dim password As String = "123456789"
Dim passwordsa As String = "123456789123"
Dim script =
<SQL>
CREATE LOGIN [<%= login %>] WITH PASSWORD=N'<%= password %>', DEFAULT_DATABASE=[<%= databaseName %>]
ALTER LOGIN [<%= domainuser %>] DISABLE
EXEC sp_password NULL, '<%= passwordsa %>', 'sa'
USE [<%= databaseName %>]
CREATE USER [<%= login %>] FOR LOGIN [<%= login %>]
ALTER USER [<%= login %>] WITH DEFAULT_SCHEMA=[dbo]
EXECUTE sp_addrolemember db_datareader, 'AZFTSUSER'
EXECUTE sp_addrolemember db_datawriter, 'AZFTSUSER'
EXECUTE sp_addrolemember db_backupoperator, 'AZFTSUSER'
</SQL>.Value
Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
Dim conn = New SqlConnection(connStr)
Dim sCmd = New SqlCommand(script, conn)
Try
conn.Open()
sCmd.ExecuteNonQuery()
MsgBox("Done : ")
Catch ex As Exception
MsgBox("Error : " & ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function
Any one have idea why first two have no problem but stop with number 3 which is "adduser2"
Continue reading...