Thinh Lam
79 lines
2.3 KiB
Transact-SQL
79 lines
2.3 KiB
Transact-SQL
-- ============================================================================
|
|
-- DYD — Create Production Database + Dedicated Login
|
|
-- Run on SQL Server as sysadmin (sa)
|
|
-- ============================================================================
|
|
|
|
USE [master];
|
|
GO
|
|
|
|
-- 1. Create database
|
|
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N'DYD_Prod')
|
|
BEGIN
|
|
CREATE DATABASE [DYD_Prod]
|
|
COLLATE Vietnamese_CI_AS;
|
|
|
|
-- Optional: set recovery model (Simple cho dev/staging, Full cho prod backup log)
|
|
ALTER DATABASE [DYD_Prod] SET RECOVERY SIMPLE;
|
|
PRINT 'Database [DYD_Prod] created.';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'Database [DYD_Prod] already exists. Skipping.';
|
|
END
|
|
GO
|
|
|
|
-- 2. Create dedicated login for the app (KHÔNG dùng sa trong production)
|
|
-- TODO: Thay '<APP_DB_PASSWORD>' bằng password mạnh (32+ char, random)
|
|
-- Generate: [System.Web.Security.Membership]::GeneratePassword(32, 8)
|
|
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = N'dyd_app')
|
|
BEGIN
|
|
CREATE LOGIN [dyd_app] WITH
|
|
PASSWORD = N'<APP_DB_PASSWORD>',
|
|
DEFAULT_DATABASE = [DYD_Prod],
|
|
CHECK_EXPIRATION = OFF,
|
|
CHECK_POLICY = ON;
|
|
PRINT 'Login [dyd_app] created.';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'Login [dyd_app] already exists. Skipping.';
|
|
END
|
|
GO
|
|
|
|
-- 3. Map login to database user + assign roles
|
|
USE [DYD_Prod];
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'dyd_app')
|
|
BEGIN
|
|
CREATE USER [dyd_app] FOR LOGIN [dyd_app];
|
|
PRINT 'User [dyd_app] created in [DYD_Prod].';
|
|
END
|
|
GO
|
|
|
|
-- Grant roles:
|
|
-- db_datareader — SELECT
|
|
-- db_datawriter — INSERT, UPDATE, DELETE
|
|
-- db_ddladmin — CREATE/ALTER/DROP (cho EF migrations)
|
|
ALTER ROLE db_datareader ADD MEMBER [dyd_app];
|
|
ALTER ROLE db_datawriter ADD MEMBER [dyd_app];
|
|
ALTER ROLE db_ddladmin ADD MEMBER [dyd_app];
|
|
PRINT 'Roles granted to [dyd_app].';
|
|
GO
|
|
|
|
-- 4. Verify
|
|
SELECT
|
|
DB_NAME() AS Database_Name,
|
|
USER_NAME() AS Current_User,
|
|
@@VERSION AS Server_Version;
|
|
GO
|
|
|
|
PRINT '';
|
|
PRINT '==========================================';
|
|
PRINT ' DONE — DYD_Prod ready.';
|
|
PRINT '==========================================';
|
|
PRINT '';
|
|
PRINT 'Connection string cho .NET (điền password đã tạo):';
|
|
PRINT 'Server=103.124.94.58,1433;Database=DYD_Prod;User Id=dyd_app;Password=<APP_DB_PASSWORD>;TrustServerCertificate=True;MultipleActiveResultSets=True;';
|
|
GO
|