How to: Define Permissions for Database Objects

You can define permissions to grant or revoke access to the database, database objects, and even specific columns in a table. To define these permissions, you must update the database.sqlpermissions file.

To open the database.sqlpermissions file

1. In Visual Studio, open the solution that contains the database project in which you want to define permissions.

2. In Solution Explorer, expand the database project node, expand the Properties node, and double-click Database.sqlpermissions.

Grant or Revoke Permissions

You can grant or revoke permissions at three levels of granularity:

• Database level   You grant or revoke permissions at this level to restrict access to operations such as CREATE TABLE.

• Database object level   You grant or revoke permissions at this level to restrict types of access to specific database objects, such as tables

• Table column level   You grant or revoke permissions at this level to restrict the types of access to specific columns in a table.

To grant database-level permissions

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="GRANT">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to grant permission. For example, to grant permission to create tables, specify CREATE TABLE.

   Replace Username with the user to whom you want to grant this permission.

3. Save and close the database.sqlpermissions file.

To revoke database-level permissions

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="REVOKE">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to revoke permission. For example, to revoke permission to create tables, specify CREATE TABLE.

   Replace Username with the user for whom you want to revoke this permission.

3. Save and close the database.sqlpermissions file.

To grant permissions on a database object

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="GRANT">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
       <Object Name="ObjectName" Schema="SchemaName" Type="OBJECT"/>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to grant permission. For example, to grant permission to select data from a table, specify SELECT.

   Replace Username with the user to whom you want to grant this permission.

   Replace ObjectName with the object to which you want to grant permission.

   Replace SchemaName with the schema in which the object is contained.

3. Save and close the database.sqlpermissions file.

To revoke permissions on a database object

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="REVOKE">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
       <Object Name="ObjectName" Schema="SchemaName" Type="OBJECT"/>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to grant permission. For example, to grant permission to select data from a table, specify SELECT.

   Replace Username with the user to whom you want to grant this permission.

   Replace ObjectName with the object to which you want to grant permission.

   Replace SchemaName with the schema in which the object is contained.

3. Save and close the database.sqlpermissions file.

To grant permissions on a column

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="GRANT">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
             <Object Name ="TableName" Schema ="SchemaName" Type ="OBJECT">
               <Columns Treatment ="INCLUDE">
                 <Column Name="ColumnName"/>
               </Columns>
             </Object>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to grant permission. For example, to grant permission to create tables, specify CREATE TABLE.

   Replace Username with the user to whom you want to grant this permission.

   Replace TableName with the object to which you want to grant permission.

   Replace SchemaName with the schema in which the object is contained.

   Replace ColumnName with the name of the column to which you want to apply permissions. You can specify multiple <Column> entries to apply permissions to multiple columns.

3. Save and close the database.sqlpermissions file.

To revoke permissions on a column

1. Open the database.sqlpermissions file.

2. Before the closing </Permissions> tag, add the following XML:

   <PermissionStatement Action="REVOKE">
       <Permission>Operation</Permission>
       <Grantee>Username</Grantee>
             <Object Name ="TableName" Schema ="SchemaName" Type ="OBJECT">
               <Columns Treatment ="INCLUDE">
                 <Column Name="ColumnName"/>
               </Columns>
             </Object>
   </PermissionsStatement>
   

   Replace Operation with the operation for which you want to grant permission. For example, to grant permission to create tables, specify CREATE TABLE.

   Replace Username with the user to whom you want to grant this permission.

   Replace TableName with the object to which you want to grant permission.

   Replace SchemaName with the schema in which the object is contained.

   Replace ColumnName with the name of the column to which you want to apply permissions. You can specify multiple <Column> entries to apply permissions to multiple columns.

3. Save and close the database.sqlpermissions file.

See Also

Tasks

How to: Create a Database Object

How to: Modify Database Objects

Concepts

Creating and Modify Database and Server Objects