Information Schema Views in SQL Server 2005

Hi

I am having a minor problem with SQL Server 2005.

I have a requirement that certain users (developers) should have visibility of certain schema information using either Management Studio, or from an application...

The problem is that when we do a query on the information schema views (from accounts that have db_DataReader and db_DataWriter and Public role membership) the column_default value is not returned, (a null value is returned) even if there is definately a default value constraint.

What permisions do I have to give these individuals, dbo is NOT an option as they should only have restriceted access to the Database.

I look forward to your answers.

Kind regards

Ronnie

As for as this one is queried within the schemaview you should either have access to the function
object_definition or the syscolums table, which you should have if you see the column names. (Don′t think that there is a more granular permission set than on the table)

object_definition(c.default_object_id)) AS COLUMN_DEFAULT,

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

The minimum permission required to view the default information for a table column is "ALTER" permission on the table.

Of course, ALTER also means that the principals with that permission can ALTER the table schema or even drop the table. If you wish to restrict that and want the principals to only view the table schema including the definition of the default constraint grant them "VIEW DEFINITION" permission on the table.

Please refer to the topic "Metadata visibilty configuration" in books online which explains this in greater detail.

http://msdn2.microsoft.com/en-us/library/ms187113.aspx

Hope that helps

Thanks

Asvin

|||Interesting point, I was afraid of metadata security in SQL 2k5, but why the hell did you connect it to ALTER ? This thing is not explained in the BOL article.

-Jens.|||

It is not always connected to ALTER. It depends on what kind of metadata you are looking for. If it's just the name or id of the table or procedure then any permission on the object is sufficient. We use a higher privilege like ALTER only when you need access to "business logic". By business logic I mean things like the body of the stored procedure, definition of a computed column, the definition of a default or check constraint.

Take a look at http://msdn2.microsoft.com/en-us/library/ms191507.aspx.

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:
> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexed Views

I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.

Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.

Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).SEE BOL
indexed views --> SET Options That Affect Results

These six SET options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.

all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
( I think this also applies for the other settings)

The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

Tim S|||always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.|||I have no computed columns. If I am understanding this right, any session issuing INSERT, UPDATE, or DELETE queries must have same session properties as the session that created the index. Does this mean that if the base table (and it's indexes) were created by one session, but the indexed view was created with a different session (with different session properties), there will always be an error when trying to query the base table?

For example, in my case it seems that the base table was created with the session property "ARITHABORT" turned off. Indexed views, however, require ARITHABORT to be on. Does this mean that I cannot create an indexed view on that base table since the view's index will always require ARITHABORT to be on and the base table index will always require it to be off?

SEE BOL
all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
( I think this also applies for the other settings)

The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

Tim S|||This isn't a case of backend software per se. It is a mutually shared database, but it was configured by them. They put their data in, we put ours in, and then we generate reports.

Either way, yes, we always backup stuff before messing with it.

always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.|||Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting...

preferably on another box, or at least a separate instance...|||That would be a great solution if the data was not needed in real time.

Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting...

preferably on another box, or at least a separate instance...|||replication?