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.

Info on Load Testing Tools Needed

Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.
Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/de...actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.
|||Thanks--looks like good stuff!
"oj" wrote:

> Here is a huge list:
> http://www.softwareqatest.com/qatweb1.html
> You could also use, MS ACT:
> http://msdn.microsoft.com/library/de...actml_main.asp
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
>
>

Info on Load Testing Tools Needed

Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/act/htm/actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.

indexes and query optimization

I have web page that users use to specify sort and selection critieria for a query. So there is a "most common" type set of where clauses, but they can change. Every time the query is run, there are where clauses on multiple columns, and joins from some columns to other tables, and then it is sorted by a different column.

I am wondering if I should have a clustered index on this table since there are so many columns used in where clauses. Would it be useful to cluster on the column it is sorted by, or should I just stick with nonclustered indexes?
ThanksIf you have sort order by different columns, clustered index will not help.
You can have only one clustered index over a table.
But you can still have none clustered indexes over columns you do order by over.

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

Hi Rama,

You can use the option EXPAND VIEWS in SELECT clause for your purpose.

Microsoft recommend's, however, to let the query optimizer dynamically determine the best access methods to use for each individual query

Jag

|||

Can u explain me in detail.if possible with example becz i'm new to DB stuff.

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

First of all you can't create a index on your view.

Reason: suser_sname is not a deterministic function. So it wont to allow you to create a index on this..

|||

Can u explain me in detail .I’m new to indexed views.

Can we create indexed view for all data in tables and can be filtered this data by referencing this indexed view with in another view? Can u consider this point also.

If this is possible then, is there any way restrict not to use indexed views by query optimizer .