Microsoft SQL Server Database User Setup

This guide explains how to create a dedicated Microsoft SQL Server database user with the appropriate permissions for executing SQL queries and retrieving schema information in your Text-to-SQL application.

Prerequisites

• Access to a Microsoft SQL Server with administrative privileges
• SQL Server Management Studio (SSMS) or Azure Data Studio
• Basic knowledge of SQL Server security concepts

Creating a Database User

Using SQL Server Management Studio (SSMS)

1. Connect to your SQL Server instance using SSMS with administrative privileges

2. Create a new login:

   • In Object Explorer, expand the server node
   • Right-click on the "Security" folder
   • Select "New > Login"
   • Enter a login name (e.g., texttosql_login)
   • Select "SQL Server authentication"
   • Enter and confirm a secure password
   • Uncheck "User must change password at next login"
   • Click "OK" to create the login

3. Create a database user for the login:

   • In Object Explorer, expand the "Databases" folder
   • Expand your target database
   • Expand the "Security" folder
   • Right-click on "Users" and select "New User"
   • Enter a user name (e.g., texttosql_user)
   • Set the login name to the login you created
   • In the "Schemas" page, ensure the user has access to necessary schemas (typically "dbo")
   • Click "OK" to create the user

4. Assign appropriate permissions:

   • In Object Explorer, navigate to your database > Security > Users
   • Right-click on your new user and select "Properties"
   • Select "Securables" in the left pane
   • Click "Search" and select "All objects of the types..."
   • Select "Tables" and "Views" and click "OK"
   • In the list of objects, grant "SELECT" permissions to all tables
   • Click "OK" to apply the permissions

Using T-SQL Commands

You can also create the user and assign permissions using T-SQL:

1. Connect to your SQL Server using SSMS or Azure Data Studio
2. Execute the following T-SQL commands:

-- Create a new SQL Server login
CREATE LOGIN texttosql_login WITH PASSWORD = 'your_secure_password';
 
-- Use your target database
USE your_database;
 
-- Create a database user for the login
CREATE USER texttosql_user FOR LOGIN texttosql_login;
 
-- Add user to the db_datareader role for read access to all tables
EXEC sp_addrolemember 'db_datareader', 'texttosql_user';
 
-- Grant EXECUTE permission on database (needed for some schema queries)
GRANT VIEW DEFINITION TO texttosql_user;
 
-- If you need the user to execute other types of queries (INSERT, UPDATE, DELETE)
-- add the appropriate role:
-- EXEC sp_addrolemember 'db_datawriter', 'texttosql_user';

Verifying the Setup

To verify that the user has the correct permissions:

1. Connect to SQL Server using the new login:

   • Open SSMS or Azure Data Studio
   • Enter the server name
   • Select "SQL Server Authentication"
   • Enter the login name and password you created
   • Connect to your database

2. Try to retrieve schema information:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dbo';

3. Try executing a simple query:

SELECT TOP 5 * FROM your_table;

Connection Configuration

When configuring your Text-to-SQL application, use these credentials:

• Host: Your SQL Server hostname or IP
• Port: 1433 (default SQL Server port)
• Database: Your database name
• Username: texttosql_login
• Password: your_secure_password

Security Considerations

• Use a strong, unique password for the database login
• Consider enabling SQL Server's encryption features
• Limit the user's permissions to only what is necessary
• Regularly audit the user's permissions to ensure they remain appropriate
• Store connection credentials securely and never expose them in client-side code
• Consider using Windows Authentication if your environment supports it