Create Login User in SQL Server

JDBC for SQL Server - Herong's Tutorial Examples

∟Create Login User in SQL Server

This section describes how to create a new login user and grant full permission to a database in SQL Server.

In order to do some tests JDBC API from Java programs, I need to enable SQL Server login and create a regular login user "Herong" to access the test database, AdventureWorks.

1. Enable SQL Server login mode:

herong> sqlcmd -S localhost\SQLEXPRESS -E

1> SELECT
2> CASE
3>  WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1
4>  THEN 'Windows Authentication Only'
5>  WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0
6>  THEN 'SQL and Windows Authentication'
7>  ELSE 'Configuration Error'
8> END as Authentication_Mode
9> go

Authentication_Mode
------------------------------
Windows Authentication Only

1> EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
2>  N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode',
3>  REG_DWORD, 2
3> GO

2. Restart SQL Server and verify the Authentication Mode:

	
herong> sqlcmd -S localhost\SQLEXPRESS -E

1> SELECT
2> CASE
3>  WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1
4>  THEN 'Windows Authentication Only'
5>  WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0
6>  THEN 'SQL and Windows Authentication'
7>  ELSE 'Configuration Error'
8> END as Authentication_Mode
9> go

Authentication_Mode
------------------------------
SQL and Windows Authentication

3. Create a new login user, "Herong", and granted full permission to "Herong" to use the test database, AdventureWorks:

	
herong> sqlcmd -S localhost\SQLEXPRESS -E

1> -- Set AdventureWorks as the current database
2> USE AdventureWorks2019;
3> GO
Changed database context to 'AdventureWorks2019'.

1> -- Create a new server login name: Herong
2> CREATE LOGIN Herong WITH PASSWORD = 'T0pSecret';
3> GO

1> -- Create a new database user linked to the login name
2> CREATE USER Herong FOR LOGIN Herong;
3> GO

1> -- Grant database ALTER permission to the user
2> GRANT ALTER To Herong;
3> GO

1> -- Grant database CONTROL permission to the user
2> GRANT CONTROL To Herong;
3> GO

Here is what I did to test this new login name and user: Herong

herong> sqlcmd -S localhost\SQLEXPRESS -U Herong -P T0pSecret

1> -- Set AdventureWorks as the current database
2> USE AdventureWorks2019;
3> GO
Changed database context to 'AdventureWorksLT'.

1> -- Create a new table
2> CREATE TABLE Test (ID INT);
3> GO

1> -- Drop the table
2> DROP TABLE Test;
3> GO