SQL Server users

This page describes how Cloud SQL works with SQL Server users and roles. SQL Server roles enable you to control the access and capabilities of users who access a SQL Server instance.

For information about creating and managing Cloud SQL users, see Creating and managing users.

SQL Server roles and users

SQL Server roles can be a single role, or they can function as a group of roles.

A user is a role with the ability to log in (the role has the LOGIN permission). All roles created by Cloud SQL have the LOGIN permission, so Cloud SQL uses the terms "role" and "user" interchangeably. However, if you create a role with the client, it does not necessarily have the LOGIN permission.

All SQL Server users must have a password. You cannot login with a user that does not have a password.

Superusers and system stored procedures

Cloud SQL for SQL Server is a managed service, so it restricts access to certain system stored procedures and tables that require advanced privileges. In Cloud SQL, you cannot create or have access to users with superuser permissions.

Default SQL Server users

When you create a new Cloud SQL for SQL Server instance, the default sqlserver user is already created for you, although you must set its password.

The sqlserver user is part of the CustomerDbRootRole role, and its permissions (privileges) include the following:

  • CONNECT SQL
  • CREATE ANY DATABASE
  • ALTER ANY LOGIN
  • ALTER ANY CONNECTION
  • ALTER TRACE
  • VIEW ANY DATABASE
  • VIEW ANY DEFINITION
  • VIEW SERVER STATE
  • ALTER SERVER STATE
  • CREATE SERVER ROLE
  • ALTER ANY SERVER ROLE

Granting server permissions

When you grant privileges using any GRANT command, you must pass CustomerDbRootRole as, for example, the value of grantor_principal.

The following GRANT ALTER ANY LOGIN example is valid:

GRANT ALTER ANY LOGIN TO [Account] AS CustomerDbRootRole

The following GRANT ALTER ANY LOGIN example is invalid:

GRANT ALTER ANY LOGIN TO [Account]

Other SQL Server users

You can create other SQL Server users or roles. All users you create using Cloud SQL are created as part of the CustomerDbRootRole role, and have the same set of permissions as the sqlserver user. You can change the permissions of any user by using the ALTER ROLE command.

If you create a new user with the client, you can choose to associate it with a different role, or give it different permissions.

Troubleshooting

Error accessing database

When trying to access to a database you created, as a user you created, you get the error The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context.

The issue might be

The user is not a member of the database.

Things to try

Connect to the database as the sqlserver user and add the new user, then give the new user the db_owner role for the database. For example:

EXEC sp_adduser 'user';
EXEC sp_addrolemember 'db_owner', 'user'

What's next