Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sys.sql_modules.definition IS NULL for non encrypted views in some of our databases Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 8:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 502, Visits: 270
Hi everyone,

we are using SQL Server 2012 Enterprise Edition since approx. 2 months.

We recently noticed that the value of the "definition" column of the sys.sql_modules table is null for all our views (these views are not encrypted) in some of the databases, in some other databases this column is correctly set.

Anyone having the same problem?

Best regards.

Carl



Post #1477094
Posted Wednesday, July 24, 2013 3:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
Funny. And it only happens to views? What happens if you try to script these views?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1477301
Posted Thursday, July 25, 2013 6:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 502, Visits: 270
Hi Erland,

Stored procedures behaves the same.

I just noticed that when I look at the proterties of these views (in SSMS) being connected as a sysadmin user, the views appears not to be encrypted (as I wrote earlier). But when I connect in SSMS with a more restrictive user these views appears to be encrypted.

So when I query the sys.sql_modules.definition column being a sysadmin, I see the content of the definition column but with a more restrictive user I can't.

It makes sense and it is related to security.

Case close.

Best regards.

Carl



Post #1477447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse