Let’s start by stating I’m not a SQL DBA 😉 when it comes to databases, I’m just a user who needs a database for my applications 🙂 Lately, in various environments, we’ve been creating different Ivanti Workspace Control and Automation Manager databases. We simply request an empty database from the SQL department and are assigned DBO rights. Subsequently, we establish a connection to the database from Ivanti Automation Manager (for example) and handle the initial database setup within the application itself.
After initializing the SQL database, the user who performed the initialization can start Ivanti Workspace Control, for example, without encountering any issues. Another user, who has the same permissions (DBO) on the database, is unable to launch the application. The second user is able to access the SQL Database using SQL Management Studio, where they seem to have full control over the database.
While comparing a ‘working database’ with the ‘non-working’ one, we noticed a distinction in SQL table names. In the working database, all tables were named like dbo.table1, dbo.table2, and so forth. In the non-working SQL Database, we observed that all table names began with the username of the individual who initially initialized the database. For example, within the non-functional SQL Database, we observed [Mydomain\Username].table1, etc.
After some investigation, we found out there were several reasons why this might happen. Here’s an overview of the main causes:
1. Default Schema of the User
In SQL Server, each database user can be assigned a default schema. If a user does not explicitly specify a schema when creating a table, the table will be created in their default schema. If the default schema is not set to dbo (which is the default schema for most users), the tables may end up in another schema that includes the domain and username.
Solution: Set the user’s default schema to dbo:
ALTER USER [domain\username] WITH DEFAULT_SCHEMA = dbo;2. Explicit Schema Specification in SQL Statements
If the SQL statements being executed to create tables include the schema explicitly as domain\username, the tables will be created under that schema.
Solution: Ensure that the SQL statements use dbo or omit the schema to use the default schema:
CREATE TABLE dbo.TableName (Column1 INT, Column2 NVARCHAR(50));3. Incorrect Connection Context
Sometimes, the connection context or the tool used to run the SQL scripts might be causing the tables to be created with the domain\username schema. This can happen if the tool implicitly uses the connected user’s name as the schema.
Solution:
- Check the connection settings and the context in which the scripts are being executed.
- Make sure that the connection is not enforcing a different schema context.
4. Lack of Permissions
If the user creating the tables does not have the necessary permissions on the dbo schema, SQL Server might default to using a schema that the user does have permissions on, which could be their own domain\username schema.
Solution: Grant the necessary permissions to the user on the dbo schema:
GRANT ALTER ON SCHEMA::dbo TO [domain\username];In our case, the SQL tables were owned by the initial user rather than dbo, requiring us to revert the ownership back to dbo. We ran a SQL query, replacing ‘DOMAIN\USERNAME’ with the current table names, which automatically generated a script to transfer all tables back to dbo ownership.
SELECT 'ALTER SCHEMA dbo TRANSFER [' + s.Name + '].' + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = 'DOMAIN\USERNAME'
And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')The query doesn’t make any changes immediately; it only generates a SQL script, which can then be used to actually change the tables. The sample output of the query-generated SQL script appears as follows:
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblAudits
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblSettings
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblObjects
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblResources
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblFiles
ALTER SCHEMA dbo TRANSFER [DOMAIN\USERNAME].tblFolders
Before proceeding with the table changes, ensure you have a valid SQL backup of the database. This step is essential to safeguard against any potential data loss or unforeseen issues.
This script contains a series of ALTER SCHEMA statements transferring ownership of each table from [DOMAIN\USERNAME] to dbo. Copy the script output generated by the query and execute it on the incorrect database. This action will transfer ownership of all tables from [DOMAIN\USERNAME] to dbo.
Now that we have ‘fixed’ the database tables, let’s ensure this doesn’t happen again by modifying the current SQL configuration to prevent new tables from being created incorrectly.
1. Set Default Schema for Users
Ensure that all new and existing database users have their default schema set to dbo. This can be done when creating the user or later if the user already exists.
Setting Default Schema When Creating a User:
CREATE USER [domain\username] FOR LOGIN [domain\username] WITH DEFAULT_SCHEMA = dbo;Altering Default Schema for Existing Users:
ALTER USER [domain\username] WITH DEFAULT_SCHEMA = dbo;2. Create Database Roles with Specific Permissions
Instead of assigning permissions directly to individual users, create roles with the necessary permissions and set the default schema for these roles to dbo.
Creating a Role with Default Schema:
CREATE ROLE [RoleName] AUTHORIZATION [dbo];
ALTER ROLE [RoleName] ADD MEMBER [domain\username];Hopefully, this article prevents you from encountering the same issue we experienced 🙂