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

For information about creating and managing Cloud SQL users, seeCreatingand 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 theLOGINpermission). All roles created by Cloud SQL have theLOGIN permission,so Cloud SQL uses the terms "role" and "user" interchangeably. However, ifyou create a role with any client tool that is compatible with SQL Server, thenthe role doesn't necessarily receive theLOGIN permission.

Cloud SQL for SQL Server restricts access to certain server roles thatprovide advanced privileges. Examples of these roles areDbRootRole,serveradmin,securityadmin,setupadmin,diskadmin, anddbcreator.

All SQL Server users must have a password. Thus, you can't log in with a userthat lacks a password.

Superusers and system stored procedures

Cloud SQL for SQL Server is a managed service, so it restricts access to certainsystem stored procedures and tables that require advanced privileges. InCloud SQL, you cannot create or have access to users with superuserpermissions.

You can also addcloudsql enable linked serversto your instance if you want to use it with linked servers. This flag grants thefollowing permission to your server:

• ALTER ANY LINKED SERVER

Granting server permissions

When you grant privileges using anyGRANTcommand,you must passCustomerDbRootRole as, for example, the value ofgrantor_principal.

The followingGRANT ALTER ANY LOGIN example isvalid:

GRANTALTERANYLOGINTO[Account]ASCustomerDbRootRole

The followingGRANT ALTER ANY LOGIN example isinvalid:

GRANTALTERANYLOGINTO[Account]

Other SQL Server users

You cancreate other SQL Server users or roles. All users youcreate using Cloud SQL are granted the same database permissions as thesqlserver 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 samepermissions as the customer administrator accounts or thesqlserver user. Forexample, if you use thecreatelogin process, and add the login to theCustomerDbRootRole server role, the userwon't have the same permissions as the customer administrator accounts or thesqlserver user. Therefore, you can use Cloud SQL to create a user if youintend the user to have the same database permissions as thesqlserver login.To validate the difference in permissions between any two accounts, you can usethe following function:sys.fn_my_permissions.

Database imports: owner permissions

When you import a database, the treatment of the owner varies as follows, basedon the type of owner:

• For an existing login that isn'tsa: Cloud SQL keeps that ownerand creates a user calledsqlserver that maps to the loginsqlserver.Cloud SQL grants theCONTROL andALTER ANY USER permissions tothatsqlserver user.
• For unknown logins, or logins that were system-created: Cloud SQLtransfers ownership of the database to thesqlserver login.

Changing permissions for users

TheALTERROLE command is available for changing user permissions. If you create a new userwith a client, you can associate it with a different role or provide differentpermissions.

Troubleshooting

Error accessing database

When trying to access to a database you created, as a user you created, you getthe following error:

TheserverprincipalUSERNAMEisnotabletoaccessthedatabaseDATABASE_NAMEunderthecurrentsecuritycontext.

The issue might be

The user is not a member of the database.

Things to try

Connect to the database as thesqlserver user and add the new user,thengive the new user thedb_owner role for the database. Forexample:

EXECsp_adduser'user';EXECsp_addrolemember'db_owner','user'

What's next

• Configure the user for your instance.
• Create and manage users.
• Create and managedatabases.