CODEWITHSUNDEEP
sql-server
maddy
maddy

Reputation: 358

Computed column definition is null

(A.) The below is the condition of "d_no" column in table test. Here you can see that it is a computed column and its definition is NULL.So I want to know that how this case is possible to generate? I am unable to create a computed column with NULL definition.

table name : test
column name : d_no
is_nullable : 1
definition : NULL
is_computed : 1

This case is for SQL Server 2012 (11.0.6523) version.

(B.) Scenario : There is "User1" who is real database owner(own database). There is another user "User2". The User2 has some permissions to access the database of User1, like SELECT or get DDL. When I login with User2 and tried to get CREATE query for the table which has a column as Computed Column. For that table we are not getting computed column definition in the result query. We are getting xyz (numeric (18,0) null)

So is it possible that there also same permission issue is occur which may result null in previous case(A)?

Upvotes: 4

Views: 1165

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28930

You can't get null definition.Even creating computed column as null,won't change the defintion to null..

create table dbo.t4
(
id int null,
idplus as cast(null as int)
)

select definition 
from sys.computed_columns

Output:

(CONVERT([int],NULL,0))

You are seeing NULL defintion due to permissions issue..

Below is what MSDN has to say about this..

The rules for viewing the Transact-SQL source code of database objects are stricter than for viewing metadata.

To see the Transact-SQL definition of an object, a user must either own the object or have been granted one of the following permissions on the object:

CONTROL
ALTER
TAKE OWNERSHIP
VIEW DEFINITION

Same is the case with below views as well,if you don't have permissions,Definition would be null..

enter image description here

Upvotes: 3

maddy
maddy

Reputation: 358

a) If the user is not the table owner of myTable and has been granted only SELECT permission on the table, when the user accesses the following catalog views, the definition columns are NULL.

Mean a user is having only select (datareader) permission instead dbowner . Then login with that user and check the definition for the computed column.

select definition
from sys.computed_columns

It gives you NULL value in definition field.

b) If you want to get the definition of computed column in the definition field for the same user as above, who have only SELECT permission, then grant the VIEW DEFINITION permission for the particular object (table having computed column) via user.

Then again login with that user and run the query to see definition value. That time it will show you actual definition of computed column in definition field.

Upvotes: 0

Related Questions