How do I grant permisions to user(s)

Question

Using the query, not the GUI. I created a user for AdventureWork2014 with the default_schema person. How do I give him permissions to use the "sale" schema as well?

Answer 1

Depending on what permissions you want to give the user on the sale schema, you could use this query:

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: sale TO user ;

Answer 2

GRANT permission  [ ,...n ] ON SCHEMA :: schema_name  
TO database_principal [ ,...n ]  
[ WITH GRANT OPTION ]  
[ AS granting_principal ]  

Arguments

• permission
  Specifies a permission that can be granted on a schema. For a list of the permissions, see the Remarks section later in this topic..

• ON SCHEMA :: schema_name
  Specifies the schema on which the permission is being granted. The scope qualifier :: is required.

• database_principal
  Specifies the principal to which the permission is being granted. One of the following:

  • database user
  • database role
  • application role
  • database user mapped to a Windows login
  • database user mapped to a Windows group
  • database user mapped to a certificate
  • database user mapped to an asymmetric key
  • database user not mapped to a server principal.

• GRANT OPTION
  Indicates that the principal will also be given the ability to grant the specified permission to other principals.

• AS granting_principal
  Specifies a principal from which the principal executing this query derives its right to grant the permission. One of the following:

  • database user
  • database role
  • application role
  • database user mapped to a Windows login
  • database user mapped to a Windows group
  • database user mapped to a certificate
  • database user mapped to an asymmetric key
  • database user not mapped to a server principal.

For further reading, please refer to the entry on Microsoft Docs, GRANT Schema Permissions.