Skip to content

Commit 78ba72f

Browse files
WilliamDAssafMSFTWilliamDAssafMSFT
committed
20210819 1417
1 parent 390b998 commit 78ba72f

6 files changed

Lines changed: 74 additions & 75 deletions

File tree

‎docs/relational-databases/security/metadata-visibility-configuration.md‎

Lines changed: 20 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@ monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-s
2525
[!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW ](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
2626
The visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table `myTable`.
2727

28-
```
28+
```sql
2929
SELECT name, object_id
3030
FROM sys.tables
3131
WHERE name = N'myTable';
@@ -79,9 +79,9 @@ GO
7979

8080
- Queries on system views might only return a subset of rows, or sometimes an empty result set.
8181

82-
- Metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return NULL.
82+
- Metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return `NULL`.
8383

84-
- The [!INCLUDE[ssDE](../../includes/ssde-md.md)] **sp_help** stored procedures might return only a subset of rows, or NULL.
84+
- The [!INCLUDE[ssDE](../../includes/ssde-md.md)] `sp_help` stored procedures might return only a subset of rows, or `NULL`.
8585

8686
SQL modules, such as stored procedures and triggers, run under the security context of the caller and, therefore, have limited metadata accessibility. For example, in the following code, when the stored procedure tries to access metadata for the table `myTable` on which the caller has no rights, an empty result set is returned. In earlier releases of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], a row is returned.
8787

@@ -95,7 +95,7 @@ END;
9595
GO
9696
```
9797

98-
To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level or server level. Therefore, in the previous example, if the caller has VIEW DEFINITION permission on `myTable`, the stored procedure returns a row. For more information, see [GRANT (Transact-SQL)](../../t-sql/statements/grant-transact-sql.md) and [GRANT Database Permissions (Transact-SQL)](../../t-sql/statements/grant-database-permissions-transact-sql.md).
98+
To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level, or server level. Therefore, in the previous example, if the caller has VIEW DEFINITION permission on `myTable`, the stored procedure returns a row. For more information, see [GRANT (Transact-SQL)](../../t-sql/statements/grant-transact-sql.md) and [GRANT Database Permissions (Transact-SQL)](../../t-sql/statements/grant-database-permissions-transact-sql.md).
9999

100100
You can also modify the stored procedure so that it executes under the credentials of the owner. When the procedure owner and the table owner are the same owner, ownership chaining applies, and the security context of the procedure owner enables access to the metadata for `myTable`. Under this scenario, the following code returns a row of metadata to the caller.
101101

@@ -124,13 +124,13 @@ GO
124124

125125
The following metadata is not subject to forced disclosure:
126126

127-
- The value stored in the `provider_string` column of `sys.servers`. A user that does not have ALTER ANY LINKED SERVER permission will see a NULL value in this column.
127+
- The value stored in the `provider_string` column of `sys.servers`. A user that does not have **ALTER ANY LINKED SERVER** permission will see a `NULL` value in this column.
128128

129129
- Source definition of a user-defined object such as a stored procedure or trigger. The source code is visible only when one of the following is true:
130130

131-
- The user has VIEW DEFINITION permission on the object.
131+
- The user has **VIEW DEFINITION** permission on the object.
132132

133-
- The user has not been denied VIEW DEFINITION permission on the object and has CONTROL, ALTER, or TAKE OWNERSHIP permission on the object. All other users will see NULL.
133+
- The user has not been denied **VIEW DEFINITION** permission on the object and has **CONTROL**, **ALTER**, or **TAKE OWNERSHIP** permission on the object. All other users will see `NULL`.
134134

135135
- The definition columns found in the following catalog views:
136136

@@ -148,16 +148,16 @@ GO
148148

149149
- The following columns in the information schema views:
150150

151-
- INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE
152-
- INFORMATION_SCHEMA.DOMAINS.DOMAIN_DEFAULT
153-
- INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION
154-
- INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT
155-
- INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULT
156-
- INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION
151+
- `INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE`
152+
- `INFORMATION_SCHEMA.DOMAINS.DOMAIN_DEFAULT`
153+
- `INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION`
154+
- `INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT`
155+
- `INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULT`
156+
- `INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION`
157157

158158
- OBJECT_DEFINITION() function
159159

160-
- The value stored in the password_hash column of `sys.sql_logins`. A user that does not have CONTROL SERVER permission will see a NULL value in this column.
160+
- The value stored in the password_hash column of `sys.sql_logins`. A user that does not have **CONTROL SERVER** permission will see a `NULL` value in this column.
161161

162162
> [!NOTE]
163163
> The SQL definitions of built-in system procedures and functions are publicly visible through the `sys.system_sql_modules` catalog view, the `sp_helptext` stored procedure, and the OBJECT_DEFINITION() function.
@@ -180,20 +180,20 @@ GO
180180
Metadata that can be accessed by fixed roles depends upon their corresponding implicit permissions.
181181

182182
### Scope of Permissions
183-
Permissions at one scope imply the ability to see metadata at that scope and at all enclosed scopes. For example, SELECT permission on a schema implies that the grantee has SELECT permission on all securables that are contained by that schema. The granting of SELECT permission on a schema therefore enables a user to see the metadata of the schema and also all tables, views, functions, procedures, queues, synonyms, types, and XML schema collections within it. For more information about scopes, see [Permissions Hierarchy (Database Engine)](../../relational-databases/security/permissions-hierarchy-database-engine.md).
183+
Permissions at one scope imply the ability to see metadata at that scope and at all enclosed scopes. For example, **SELECT** permission on a schema implies that the grantee has **SELECT** permission on all securables that are contained by that schema. The granting of **SELECT** permission on a schema therefore enables a user to see the metadata of the schema and also all tables, views, functions, procedures, queues, synonyms, types, and XML schema collections within it. For more information about scopes, see [Permissions Hierarchy (Database Engine)](../../relational-databases/security/permissions-hierarchy-database-engine.md).
184184

185185
> [!NOTE]
186-
> The UNMASK permission does not influence metadata visibility: granting UNMASK alone will not disclose any Metadata. UNMASK will always need to be accompanied by a SELECT permission to have any effect. Example: granting UNMASK on database scope and granting SELECT on an individual Table will have the result that the user can only see the metadata of the individual table from which he can select, not any others.
186+
> The **UNMASK** permission does not influence metadata visibility: granting **UNMASK** alone will not disclose any Metadata. **UNMASK** will always need to be accompanied by a **SELECT** permission to have any effect. Example: granting **UNMASK** on database scope and granting **SELECT** on an individual Table will have the result that the user can only see the metadata of the individual table from which they can select, not any others.
187187
188188
### Precedence of DENY
189-
DENY typically takes precedence over other permissions. For example, if a database user is granted EXECUTE permission on a schema but has been denied EXECUTE permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.
189+
**DENY** typically takes precedence over other permissions. For example, if a database user is granted **EXECUTE** permission on a schema but has been denied **EXECUTE** permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.
190190

191-
Additionally, if a user is denied EXECUTE permission on a schema but has been granted EXECUTE permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.
191+
Additionally, if a user is denied **EXECUTE** permission on a schema but has been granted **EXECUTE** permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.
192192

193-
For another example, if a user has been granted and denied EXECUTE permission on a stored procedure, which is possible through your various role memberships, DENY takes precedence and the user cannot view the metadata of the stored procedure.
193+
For another example, if a user has been granted and denied **EXECUTE** permission on a stored procedure, which is possible through your various role memberships, **DENY** takes precedence and the user cannot view the metadata of the stored procedure.
194194

195195
### Visibility of Subcomponent Metadata
196-
The visibility of subcomponents, such as indexes, check constraints, and triggers is determined by permissions on the parent. These subcomponents do not have grantable permissions. For example, if a user has been granted some permission on a table, the user can view the metadata for the tables, columns, indexes, check constraints, triggers, and other such subcomponents. Another example is granting SELECT on only an individual column of a given table: this will allow the grantee to view the matadata of the whole table, including all columns. One way to think of it, is that the VIEW DEFINITION permission only works on entity-level (the table in this case) and is not available for Sub-entity lists (such as column or security expressions).
196+
The visibility of subcomponents, such as indexes, check constraints, and triggers is determined by permissions on the parent. These subcomponents do not have grantable permissions. For example, if a user has been granted some permission on a table, the user can view the metadata for the tables, columns, indexes, check constraints, triggers, and other such subcomponents. Another example is granting **SELECT** on only an individual column of a given table: this will allow the grantee to view the metadata of the whole table, including all columns. One way to think of it, is that the **VIEW DEFINITION** permission only works on entity-level (the table in this case) and is not available for Sub-entity lists (such as column or security expressions).
197197

198198
The following code demonstrates this behavior:
199199

‎docs/relational-databases/stored-procedures/view-the-definition-of-a-stored-procedure.md‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-s
2121

2222
## <a name="Top"></a> View the definition of a stored procedure
2323

24-
This topic describes how to view the definition of procedure in Object Explorer and by using a system stored procedure, system function, and object catalog view in the Query Editor.
24+
This article describes how to view the definition of procedure in Object Explorer and by using a system stored procedure, system function, and object catalog view in the Query Editor.
2525

2626
- **Before you begin:** [Security](#Security)
2727

@@ -33,10 +33,10 @@ This topic describes how to view the definition of procedure in Object Explorer
3333

3434
#### <a name="Permissions"></a> Permissions
3535
System Stored Procedure: **sp_helptext**
36-
Requires membership in the **public** role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.
36+
Requires membership in the **public** role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: **ALTER**, **CONTROL**, **TAKE OWNERSHIP**, or **VIEW DEFINITION**.
3737

3838
System Function: **OBJECT_DEFINITION**
39-
System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. These permissions are implicitly held by members of the **db_owner**, **db_ddladmin**, and **db_securityadmin** fixed database roles.
39+
System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: **ALTER**, **CONTROL**, **TAKE OWNERSHIP**, or **VIEW DEFINITION**. These permissions are implicitly held by members of the **db_owner**, **db_ddladmin**, and **db_securityadmin** fixed database roles.
4040

4141
Object Catalog View: **sys.sql_modules**
4242
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see [Metadata Visibility Configuration](../../relational-databases/security/metadata-visibility-configuration.md).
@@ -58,7 +58,7 @@ This topic describes how to view the definition of procedure in Object Explorer
5858

5959
2. Expand **Databases**, expand the database in which the procedure belongs, and then expand **Programmability**.
6060

61-
3. Expand **Stored Procedures**, right-click the procedure and then click **Script Stored Procedure as**, and then click one of the following: **Create To**, **Alter To**, or **Drop and Create To**.
61+
3. Expand **Stored Procedures**, right-click the procedure and then select **Script Stored Procedure as**, and then select one of the following: **Create To**, **Alter To**, or **Drop and Create To**.
6262

6363
4. Select **New Query Editor Window**. This will display the procedure definition.
6464

@@ -69,7 +69,7 @@ This topic describes how to view the definition of procedure in Object Explorer
6969
System Stored Procedure: **sp_helptext**
7070
1. In Object Explorer, connect to an instance of the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
7171

72-
2. On the toolbar, click **New Query**.
72+
2. On the toolbar, select **New Query**.
7373

7474
3. In the query window, enter the following statement that uses the `sp_helptext` system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want.
7575

@@ -83,7 +83,7 @@ This topic describes how to view the definition of procedure in Object Explorer
8383

8484
1. In Object Explorer, connect to an instance of the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
8585

86-
2. On the toolbar, click **New Query**.
86+
2. On the toolbar, select **New Query**.
8787

8888
3. In the query window, enter the following statements that use the `OBJECT_DEFINITION` system function. Change the database name and stored procedure name to reference the database and stored procedure that you want.
8989

@@ -97,7 +97,7 @@ This topic describes how to view the definition of procedure in Object Explorer
9797

9898
1. In Object Explorer, connect to an instance of the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
9999

100-
2. On the toolbar, click **New Query**.
100+
2. On the toolbar, select **New Query**.
101101

102102
3. In the query window, enter the following statements that use the `sys.sql_modules` catalog view. Change the database name and stored procedure name to reference the database and stored procedure that you want.
103103

‎docs/relational-databases/system-stored-procedures/sp-helptext-transact-sql.md‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -28,8 +28,7 @@ monikerRange: "=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||
2828

2929
## Syntax
3030

31-
```
32-
31+
```syntaxsql
3332
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]
3433
```
3534

@@ -56,7 +55,7 @@ sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]
5655
> The system stored procedure `sp_helptext` is not supported in Azure Synapse Analytics. Instead, use `OBJECT_DEFINITION` system function or `sys.sql_modules` object catalog view for equivalent results.
5756
5857
## Permissions
59-
Requires membership in the **public** role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.
58+
Requires membership in the **public** role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: **ALTER**, **CONTROL**, **TAKE OWNERSHIP**, or **VIEW DEFINITION**.
6059

6160
## Examples
6261

0 commit comments

Comments
 (0)