Creating SQL Database Users and Assigning Permissions
Introduction
In this guide, we will walk you through the process of creating SQL Databases and database users using SQL Server Management Studio (SSMS). Additionally, we’ll cover how to assign permissions to these users for specific databases. Database users can be used to provide your web application(s) access to specific databases that you provide permissions to. This is useful when creating a ColdFusion DSN or some other data source for your application.
Prerequisites
- Ensure you have SQL Server Management Studio installed locally on your server. All xByte servers are provisioned with SQL Server Management Studio installed if SQL Server is present.
- You have the necessary administrative privileges (signed in as the sa user).
Creating a New Database
1. Open SQL Server Management Studio
- Launch SSMS and connect to the SQL Server instance where you want to create the user.
Step 2: Create a New Database
-
Expand the server node in the Object Explorer panel on the left-hand side of the SSMS window.
-
Right-click on the “Databases” folder.
-
Select “New Database” from the context menu.
Step 3: Configure Database Properties
-
Enter a name for your new database in the “Database name” field.
- Optionally, specify the owner of the database in the “Owner” dropdown. By default, it’s the user creating the database.
-
Set the initial size of the database files (Data and Log) in the “Initial Size (MB)” field. This determines the size of the database when it’s created.
-
Configure other options such as file locations, filegroups, collation, and recovery model as per your requirements.
-
After configuring the database properties, click on the “OK” button to create the database.
Step 5: Verify Database Creation
To verify that the database was created successfully:
-
Expand the “Databases” folder in the Object Explorer.
-
Look for the newly created database in the list.
Congratulations! You have successfully created a new database in SQL Server Management Studio.
Creating Users
1. Create a New Login
- Right-click on “Security” under the desired database server.
- Choose “New” → “Login.”
2. Configure Login Properties
- Enter the login name.
- Choose the authentication type (Windows or SQL Server).
- Set a strong password if using SQL Server authentication.
- Specify the default database.
3. Assign Server Roles
- Navigate to the “Server Roles” tab.
- Assign appropriate server roles based on the user’s responsibilities (e.g., sysadmin, securityadmin).
4. Map to User in Databases
- Switch to the “User Mapping” tab.
- Select the databases where the user needs access.
- Assign database roles accordingly.
5. Set Database User Permissions
- Expand the chosen database.
- Navigate to “Security” → “Users.”
- Right-click on the newly created user and choose “Properties.”
6. Configure Database User Properties
- In the “Membership” section, select the desired database.
- Assign appropriate roles within the database (e.g., db_owner, db_datareader).
Conclusion
You’ve successfully created a SQL Database user, assigned server roles, mapped them to specific databases, and configured necessary permissions. Regularly review and update user permissions to maintain a secure and efficient database environment.
This guide provides a concise overview, but always consider your organization’s security policies and best practices when managing database users and permissions.