Create the database with tables and the user in MS SQL Server

This page gives you the database layout to use when you wish to setup an SQL to SMS solution. You will see that two database tables need to be created. One will be used for sending SMS messages and the other for receiving.

Create database

The first step is to click the New Query icon in SQL Server Management Studio Express top left corner as you can see on the Figure 1.

create new query
Figure 1 - Create new query

Into the new SQL panel, enter the create database statement and then click the Execute icon. If everything is all right, the "Command(s)completed successfully" message will be displayed as Figure 2 shows. 

create database ozekisms;

create a new database
Figure 2 - Create database

Create tables

Now you have to create the ozekimessagein and ozekimessageout tables. Delete the "create database ozekisms;" text and enter the create table statements into the SQL panel. Then, click the Execute icon as you can see on the Figure 3. If everything is all right, the "Command(s) completed successfully" message will be displayed. 

use ozekisms;

create table ozekimessagein (
id int identity(1,1),
sender varchar(30),
receiver varchar(30),
msg varchar(1024),
senttime varchar(100),
receivedtime varchar(100),
operator varchar(100),
msgtype varchar(160));

create table ozekimessageout (
id int identity(1,1),
sender varchar(30),
receiver varchar(30),
msg varchar(1024),
senttime varchar(100),
receivedtime varchar(100),
status varchar(20),
operator varchar(100),
msgtype varchar(160));

create new ozeki tables
Figure 3 - Create Ozeki tables

Create datbase user

Finally, you have to create the user (named sqluser with the abc123 password) that will have permission to log in and access the tables above. Please use the statement below and click the Execute icon as you can see on the Figure 4. If everything is all right, the "Command(s) completed successfully" message will be displayed. 

use ozekisms;
create login sqluser with password='abc123';
use ozekisms;
create user sqluser for login sqluser;
use ozekisms;
grant select,insert,update,delete on ozekimessagein to sqluser;
grant select,insert,update,delete on ozekimessageout to sqluser;

create a new user
Figure 4 - Create new user

More information