Creating a database in MSSQL using Query

Creating a database with no size limits

CREATE DATABASE <database_name>

Creating a database with size limits

USE master ;
GO
CREATE DATABASE <database name>
ON
( NAME = <mdf_filename>_dat,
FILENAME = ‘<path of mdf file to be saved after creating>’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5 MB)
LOG ON
( NAME = <ldf_filename>_log,
FILENAME = ‘<path of ldf file to be saved after creating>’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

To create a SQL Server login that uses Windows Authentication using Transact-SQL

In Query Editor, enter the following Transact-SQL command:

CREATE LOGIN <name of Windows User> FROM WINDOWS

GO     

To create a SQL Server login that uses SQL Server Authentication (Transact-SQL)

In Query Editor, enter the following Transact-SQL command:

CREATE USER <new user name> FOR LOGIN <login name>

GO 

Creating User with Password with unchecking Enforce Password Policy 

USE [master]

GO

CREATE LOGIN [<username>] WITH PASSWORD=N'<password>’, DEFAULT_DATABASE=[<database_name>],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Change default database of a login:

alter login <loginname> with default_database = <dbname>;

Create a user in a database for a given login:

use <dbname>;

create user <username> from login <loginname>;

Make an user member of db_owner group:

use <dbname>

exec sp_addrolemember ‘db_owner’, ‘<username>’;

Make a login ‘dbo’ of a database:

alter authorization on database::<dbname> to <loginname>;

Adding db_owner to database roles.

EXEC sp_addrolemember ‘db_owner’,'<username>’

Creating A table:

USE <database_name>;

CREATE TABLE <tablename>

(

XXXX INT NOT NULL PRIMARY KEY,

XXXXX NVARCHAR(30) NOT NULL,

XXXXXX NVARCHAR(30) NOT NULL

);