How can I have a db_owner permission on System Databases?

[henryleme]

I'm Database Engineer that works most with SQL Server and with experience with SQL and all SQL alternatives in Microsoft Azure and AWS, and recently had my first experience with Google Cloud Database world, and I'm having a problem with a database that I migrated from Azure MI to Google Cloud SQL Server Instance;

In Google Cloud SQL there is no such a option like sysadmin role and privileges, and I know all about that, I studied all the articles explaining about it, but I still need to have a user with db_owner privileges in all system databases like master, msdb and tempdb;

How can I work around of this missing option in Google Cloud? How I can have a USER with permission enough to give db_owner roles membership to any user that I want on SQL Server without receiving this error?

I searched for a workaround of this missing option/ difference on Google Cloud and still had no resolutions;
If anyone can please enlighten me, I would be very glad!

[ms4446]

Hi @henryleme ,

As you've noted, Google Cloud SQL does not offer the sysadmin role or direct db_owner rights on system databases such as master, msdb, and tempdb. This configuration is intentionally designed to enhance security and manageability.

To address your needs, consider the following workarounds:

Using the cloudsqladmin User:

• This user has elevated privileges that are somewhat similar to those of the sysadmin role.
• You can use this account for tasks like creating logins or managing jobs in system databases.
• Please note that it does not grant full db_owner rights, and therefore some operations may still be restricted.

Creating Dedicated Users with Tailored Permissions:

• Establish specific users within each system database, and grant only the necessary permissions (e.g., db_datareader, db_datawriter, db_ddladmin). This approach offers more granular control and adheres to the Principle of Least Privilege.

  Example:

  -- Connect with a user having sufficient privileges (e.g., cloudsqladmin)
  CREATE LOGIN limited_user WITH PASSWORD = '<strong_password>';
  USE master; -- Or msdb, tempdb as needed
  CREATE USER limited_user FOR LOGIN limited_user;
  ALTER ROLE db_datareader ADD MEMBER limited_user; -- Adjust permissions as needed
  

Utilizing SQL Agent Jobs and External Tools:

• For scheduled tasks, employ SQL Agent jobs, which can be managed using either a user with elevated permissions or those with specific permissions in msdb.
• For tasks like backup/restore or schema changes, consider using tools such as SQL Server Management Studio (SSMS) or Azure Data Studio, connecting with credentials that include elevated permissions.

These methods provide practical alternatives while maintaining compliance with Google Cloud's security policies. Always ensure that you grant only the minimum necessary permissions to users, and regularly review these permissions to ensure security and compliance.

[henryleme]

But how do I use this user cloudsqladmin?

This user doesn't exist in my Google Cloud SQL Server Instance...
The only user by default of GC SQL is the sqlserver one

[ms4446]

I apologize for the confusion regarding the cloudsqladmin user. In Cloud SQL for SQL Server, the default administrative user is indeed sqlserver, and not cloudsqladmin as might be suggested by analogies from other database systems or configurations in Google Cloud.

To work around the limitations you are facing with the sqlserver user, here are some steps and considerations:

Enhanced User Permissions:

Though the sqlserver user is the default admin, its permissions are restricted compared to a traditional sysadmin role in an on-premises SQL Server environment. If you find that the sqlserver user does not have the necessary permissions, you might need to consider alternative approaches to manage your database tasks.

Custom Roles and Permissions:

You can create custom roles and assign specific permissions that align closely with what you would need from a db_owner. This setup involves granting granular permissions tailored to the tasks at hand.

Example:

-- Creating a new role with custom permissions
CREATE ROLE custom_admin_role;
GRANT ALTER, CONTROL, CREATE TABLE, CREATE VIEW, DELETE, EXECUTE, INSERT, SELECT, UPDATE ON DATABASE::[YourDatabase] TO custom_admin_role;

[henryleme]

But for example, for what I was answered before, there is no way that I can give a permission to an user in system databases (master, msdb, tempd);
So how can I have a user that has the permissions to create and alter any Jobs in the SQL Server agent, if to be able to do that I need to have "SQLAgent" role membership?

So it's impossible for example to create an user for one of my Engineers so they can create and alter jobs in the SQL Server Agent?

[ms4446]

Unfortunately, you're right. Due to the restrictive security model in Cloud SQL for SQL Server which is managed service, granting direct SQLAgent role membership or equivalent permissions on system databases like msdb is not possible. This limitation makes it impossible to create a user with the full ability to create and alter SQL Server Agent jobs in the way you would traditionally do on an on-premises SQL Server.

However, you might want to explore some alternative approaches, though they come with their own caveats:

External Orchestration:

• Cloud Scheduler/Functions: Use Google Cloud Scheduler or Cloud Functions to trigger SQL scripts that manage your SQL Server Agent jobs. You would need to carefully craft these scripts and grant the Cloud Scheduler/Functions service account appropriate permissions to execute them on your SQL instance.
• Third-Party Tools: Explore third-party job scheduling tools that can integrate with Google Cloud SQL and provide the desired level of user control.

3. Limited User with Predefined Jobs:

• Pre-Created Jobs: Create the necessary SQL Server Agent jobs in advance, granting your engineer the ability to execute them (but not modify or create new ones). This approach provides limited flexibility but might suffice for specific use cases.

Important Considerations:

• Security: Any workaround involving external services or predefined jobs needs careful consideration of security implications. Ensure proper authentication, authorization, and auditing mechanisms are in place.
• Maintenance: Managing SQL Server Agent jobs through external tools or scripts can introduce additional complexity and maintenance overhead.

While these alternatives aren't ideal, they might offer viable solutions depending on your specific requirements and constraints. It's crucial to weigh the pros and cons of each approach and choose the one that best aligns with your security, manageability, and flexibility needs.

[henryleme]

Okay;

But I just made a test job with the default user "sqlserver" that has SQLServerAgent permissions and it worked...

So just to confirm the information;
Besides the default "sqlserver" user, it's impossible to create an user that can create and alter jobs, because of the impossibility of a permission in msdb database?
So if I need to create, edit or delete a SQL Server job, I need to use the sqlserver user... Is that it?

[ms4446]

Unfortunately, as you've identified and as is the limitation within Cloud SQL - MS SQL Server L, it's not possible to create additional users that have the same level of SQL Server Agent permissions as the sqlserver user. This is due to restrictions on granting SQLAgent role membership or equivalent permissions within the msdb database.

Given these constraints, here are your current options for managing SQL Server Agent jobs:

• Continue using the sqlserver user for all related administrative tasks. This user is equipped with the broad permissions necessary for comprehensive job management.
• External Tools and Automation: Use external orchestration tools or scripts that interact with Google Cloud SQL via the sqlserver user to manage jobs programmatically.