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 any client tool that is compatible with SQL Server, then
the role doesn't necessarily receive the LOGIN
permission.
Cloud SQL for SQL Server restricts access to certain server roles that
provide advanced privileges. Examples of these roles are DbRootRole
,
serveradmin
, securityadmin
, setupadmin
, diskadmin
, and dbcreator
.
All SQL Server users must have a password. Thus, you can't log in with a user that lacks 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:
ALTER ANY CONNECTION
ALTER ANY LOGIN
ALTER ANY SERVER ROLE
ALTER SERVER STATE
ALTER TRACE
CONNECT SQL
CREATE ANY DATABASE
CREATE SERVER ROLE
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
You can also add cloudsql enable linked servers
to your instance if you want to use it with linked servers. This flag grants the
following permission to your server:
ALTER ANY LINKED SERVER
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 granted the same database permissions as the
sqlserver
login. However, if you use a different process to create a user
(rather than creating it using Cloud SQL), the user won't have the same
permissions as the customer administrator accounts or the sqlserver
user. For
example, if you use the create
login
process, and add the login to the CustomerDbRootRole
server role, the user
won't have the same permissions as the customer administrator accounts or the
sqlserver
user. Therefore, you can use Cloud SQL to create a user if you
intend the user to have the same database permissions as the sqlserver
login.
To validate the difference in permissions between any two accounts, you can use
the following function:
sys.fn_my_permissions.
Database imports: owner permissions
When you import a database, the treatment of the owner varies as follows, based on the type of owner:
- For an existing login that isn't
sa
: Cloud SQL keeps that owner and creates a user calledsqlserver
that maps to the loginsqlserver
. Cloud SQL grants theCONTROL
andALTER ANY USER
permissions to thatsqlserver
user. - For unknown logins, or logins that were system-created: Cloud SQL
transfers ownership of the database to the
sqlserver
login.
Changing permissions for users
The ALTER ROLE command is available for changing user permissions. If you create a new user with a client, you can associate it with a different role or provide different permissions.
Troubleshooting
Error accessing database
When trying to access to a database you created, as a user you created, you get the following 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'