Boot.ini file: information

Showing posts with label information_schema. Show all posts

Thursday, March 22, 2012

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:
> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>> Hi, All
>> Recently, I was running a script in sql 2000 on window 2000 server like:
>> declare @.sql nvarchar(4000)
>> declare @.db sysname ; set @.db = DB_NAME()
>> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
>> +
>> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> INFORMATION_SCHEMA.ROUTINES ' +
>> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> exec master.dbo.xp_execresultset @.sql,@.db
>> to grant stored procedures execution permition to role, after running the
>> script, the privilige is grant it to that role, but I got very strange
>> things happened on sql server.
>> 1. Master database get Information_Shema as a user. dbaccess "via group
>> membership"
>> 2.Master database got system_function_schema as a user , dbaccess "via
>> group
>> membership"
>> 3.guest user show in every database in the instance, dbaccess "via group
>> membership"
>> if I delete guest account, show me message "the user is not in
>> database", sp_helpuser did not show guest user, enterprice manager show
>> it
>> as a user.
>> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
>> delete it successful, however, a couple minutes later, it appears in the
>> db
>> as dbaccess via group membership.
>> it is really painful for me. could anyone give me a hint to fix the
>> problem?
>> any help is appreciated.
>> Thanks
>> Yifei
>>
>>
>>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> > Hi
> >
> > You may want to try
> >
> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> > '.INFORMATION_SCHEMA.ROUTINES ' +
> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >
> > Although if that gives you thre right view you may get nothing back as the
> > OBJECT_ID function could cause problems, you may therefore want to resort
> > to
> > using sysobjects.
> >
> > John
> >
> >
> >
> >
> > "Yifei Jiang" wrote:
> >
> >> Hi, All
> >>
> >> Recently, I was running a script in sql 2000 on window 2000 server like:
> >>
> >> declare @.sql nvarchar(4000)
> >> declare @.db sysname ; set @.db = DB_NAME()
> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >>
> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
> >> +
> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >>
> >> exec master.dbo.xp_execresultset @.sql,@.db
> >>
> >> to grant stored procedures execution permition to role, after running the
> >> script, the privilige is grant it to that role, but I got very strange
> >> things happened on sql server.
> >>
> >> 1. Master database get Information_Shema as a user. dbaccess "via group
> >> membership"
> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> group
> >> membership"
> >> 3.guest user show in every database in the instance, dbaccess "via group
> >> membership"
> >> if I delete guest account, show me message "the user is not in
> >> database", sp_helpuser did not show guest user, enterprice manager show
> >> it
> >> as a user.
> >> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> >> delete it successful, however, a couple minutes later, it appears in the
> >> db
> >> as dbaccess via group membership.
> >>
> >> it is really painful for me. could anyone give me a hint to fix the
> >> problem?
> >>
> >> any help is appreciated.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >>
> >>
> >>
> >>
> >>|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>> Hi, John,
>> Thank you for replay,
>> I think the issue relate to sql 2005, because from em on the server, the
>> information_shema does not show as user, from em installed sql2005,
>> information_shema shows as user. Same as another machine that installed
>> sql
>> 2005 and does not run the script.
>> The reason because I did not check the user until I run the script and
>> have
>> to check it and find the problem.
>> Thanks
>> Yifei
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
>> > Hi
>> >
>> > You may want to try
>> >
>> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> > ''.'' +
>> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
>> > '.INFORMATION_SCHEMA.ROUTINES ' +
>> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >
>> > Although if that gives you thre right view you may get nothing back as
>> > the
>> > OBJECT_ID function could cause problems, you may therefore want to
>> > resort
>> > to
>> > using sysobjects.
>> >
>> > John
>> >
>> >
>> >
>> >
>> > "Yifei Jiang" wrote:
>> >
>> >> Hi, All
>> >>
>> >> Recently, I was running a script in sql 2000 on window 2000 server
>> >> like:
>> >>
>> >> declare @.sql nvarchar(4000)
>> >> declare @.db sysname ; set @.db = DB_NAME()
>> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> >>
>> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> >> ''.''
>> >> +
>> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> >> INFORMATION_SCHEMA.ROUTINES ' +
>> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >>
>> >> exec master.dbo.xp_execresultset @.sql,@.db
>> >>
>> >> to grant stored procedures execution permition to role, after running
>> >> the
>> >> script, the privilige is grant it to that role, but I got very strange
>> >> things happened on sql server.
>> >>
>> >> 1. Master database get Information_Shema as a user. dbaccess "via
>> >> group
>> >> membership"
>> >> 2.Master database got system_function_schema as a user , dbaccess "via
>> >> group
>> >> membership"
>> >> 3.guest user show in every database in the instance, dbaccess "via
>> >> group
>> >> membership"
>> >> if I delete guest account, show me message "the user is not in
>> >> database", sp_helpuser did not show guest user, enterprice manager
>> >> show
>> >> it
>> >> as a user.
>> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
>> >> database. I
>> >> delete it successful, however, a couple minutes later, it appears in
>> >> the
>> >> db
>> >> as dbaccess via group membership.
>> >>
>> >> it is really painful for me. could anyone give me a hint to fix the
>> >> problem?
>> >>
>> >> any help is appreciated.
>> >>
>> >> Thanks
>> >>
>> >> Yifei
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx
John
"Yifei" wrote:
> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> > The original post has no mention of SQL 2005. If you use Management
> > Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> > under system views in the master database. If you use Enterprise
> > manager to look at a SQL 2000 database there is no division between
> > system and user views. If you used a cursor instead of using the
> > undocumented xp_execresultset this should work.
> >
> > John
> >
> > Scott wrote:
> >> Hi, John,
> >>
> >> Thank you for replay,
> >>
> >> I think the issue relate to sql 2005, because from em on the server, the
> >> information_shema does not show as user, from em installed sql2005,
> >> information_shema shows as user. Same as another machine that installed
> >> sql
> >> 2005 and does not run the script.
> >>
> >> The reason because I did not check the user until I run the script and
> >> have
> >> to check it and find the problem.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> >> > Hi
> >> >
> >> > You may want to try
> >> >
> >> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> > ''.'' +
> >> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> >> > '.INFORMATION_SCHEMA.ROUTINES ' +
> >> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >
> >> > Although if that gives you thre right view you may get nothing back as
> >> > the
> >> > OBJECT_ID function could cause problems, you may therefore want to
> >> > resort
> >> > to
> >> > using sysobjects.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> >
> >> > "Yifei Jiang" wrote:
> >> >
> >> >> Hi, All
> >> >>
> >> >> Recently, I was running a script in sql 2000 on window 2000 server
> >> >> like:
> >> >>
> >> >> declare @.sql nvarchar(4000)
> >> >> declare @.db sysname ; set @.db = DB_NAME()
> >> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >> >>
> >> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> >> ''.''
> >> >> +
> >> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >>
> >> >> exec master.dbo.xp_execresultset @.sql,@.db
> >> >>
> >> >> to grant stored procedures execution permition to role, after running
> >> >> the
> >> >> script, the privilige is grant it to that role, but I got very strange
> >> >> things happened on sql server.
> >> >>
> >> >> 1. Master database get Information_Shema as a user. dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 3.guest user show in every database in the instance, dbaccess "via
> >> >> group
> >> >> membership"
> >> >> if I delete guest account, show me message "the user is not in
> >> >> database", sp_helpuser did not show guest user, enterprice manager
> >> >> show
> >> >> it
> >> >> as a user.
> >> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
> >> >> database. I
> >> >> delete it successful, however, a couple minutes later, it appears in
> >> >> the
> >> >> db
> >> >> as dbaccess via group membership.
> >> >>
> >> >> it is really painful for me. could anyone give me a hint to fix the
> >> >> problem?
> >> >>
> >> >> any help is appreciated.
> >> >>
> >> >> Thanks
> >> >>
> >> >> Yifei
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

Thursday, March 8, 2012

Bug in INFORMATION_SCHEMA?

We just confirmed that if you change the length of a varchar in a table,
INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
for views that use that varchar. You have to drop and recreate the view for
the update to occur. This is a bug in my opinion especially since MS insits
you use the views and not the underlying sys tables. We use a C# class
generator that creates code based on those values and ran into this problem.
Anyone else noticed this?
</joel>That's not a bug -- the problem is that altering a table does not alter the
view that references the table. For instance, you can alter a table and
drop a column, and the view will not be affected -- you'll find out next
time you query it, though!
One way to keep problems like this from happening is to use the WITH
SCHEMABINDING option. This will disallow changes to the base table unless
you drop the view -- meaning that the view will have to be recreated after
you alter the table, and the INFORMATION_SCHEMA will therefore get updated.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur. This is a bug in my opinion especially since MS
insits
> you use the views and not the underlying sys tables. We use a C# class
> generator that creates code based on those values and ran into this
problem.
> Anyone else noticed this?
> </joel>
>|||When you change an underlying table, view metadata (in system tables) is not
automatically updated. You need to execute sp_refreshview or ALTER or
DROP/CREATE the view.
Note that this affects not only the data returned by the INFORMATION_SCHEMA
views but also the view itself. You will get old column definitions until
the metadata is refreshed. Illustrative script:
CREATE TABLE Table1(Col1 varchar(10))
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1' -- length 10
ALTER TABLE Table1 ALTER COLUMN Col1 varchar(20)
EXEC sp_help 'View1' -- length 10
EXEC sp_refreshview 'View1'
EXEC sp_help 'View1' -- length 20
GO
We follow a standard practice of executing sp_refreshview against all views
following schema changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||That's not a bug in the INFORMATION_SCHEMA really. The metadata about the
columns that are in a view are stored with the view definition and are not
automatically updated when you change the underlying table(s). (This is a
one of the reasons why it is no good idea to use SELECT * in a view).
You don't have to drop and recreate the view for the metadata to be updated
though, you can achieve that with:
EXEC sp_refreshview '<view_name>'
Jacco Schalkwijk
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur.
Are you changing the definition of the view, or the underlying table? If
you create your view with SCHEMABINDING, you won't be able to change the
underlying table, and this won't be an issue. Otherwise, plan to run
sp_refreshview on all views that reference the table in order to update the
metadata (this applies both to
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH and syscolumns.length).
Books Online documents this quite well, in fact:
"Refreshes the metadata for the specified view. Persistent metadata for a
view can become outdated because of changes to the underlying objects upon
which the view depends."
While it would be a nice enhancement for the metadata to truly reflect the
current state, I don't think Microsoft will consider it a bug because the
behavior is documented. I also think it would be quite expensive for SQL
Server to watch for ALTER TABLE events and then go and parse all the views
that reference it and make sure their metadata is updated also...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Wow,

DBAs! :-)
I can certainly see in the case of dropping a column that SqlServer wouldn't
parse a View and try to figure out the program's intent, but a column length
is a column length.
If it walks like a bug...
</joel>
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
> That's not a bug -- the problem is that altering a table does not alter
> the
> view that references the table. For instance, you can alter a table and
> drop a column, and the view will not be affected -- you'll find out next
> time you query it, though!
> One way to keep problems like this from happening is to use the WITH
> SCHEMABINDING option. This will disallow changes to the base table unless
> you drop the view -- meaning that the view will have to be recreated after
> you alter the table, and the INFORMATION_SCHEMA will therefore get
> updated.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Joel" <joelycat@.hotmail.com> wrote in message
> news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> for
> insits
> problem.
>|||You found that response "Angry".
You haven't been in this newsgroup very long, have you?
Don't take your frustration out on people answering your questions just
because you don't like the answer.
If you don't like how SQL server handles this send your request to
Microsoft.
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23GqmeCXKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Wow,

DBAs! :-)
> I can certainly see in the case of dropping a column that SqlServer
> wouldn't parse a View and try to figure out the program's intent, but a
> column length is a column length.
> If it walks like a bug...
> </joel>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
>|||> Wow,

DBAs! :-)
Who's

?

> If it walks like a bug...
Again, a bug to you is not a bug to everybody. This behavior is well-known
and well-documented.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oqfel%23XKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Who's

?
I'm

that someone would imply that I am

.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Bug in INFORMATION_SCHEMA.SCHEMATA?

This is SQL Server 2000 with SP3a.
When I do
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
I get only a subset of databases listed. I did check the permission to the
non-listed databases, and without knowing exactly what permission "chain"
the above view uses, the user definitely "has access" to the missing
databases. I tried both with an account with very limited permissions (SQL
Server login), "sa" (SQL Server login) and Integrated Security login from a
Windows account in the (Local) Administrators group on the SQL Server
machine. The login "BUILTIN\Administrators" has SA access, and has
permissions to all the databases.
If I connect as the same user to a different ("unlisted") database, the
current database shows up in the above query, and the one that I was
previously connected to (and therefore showed up) is gone from the results.
I also tried
SELECT HAS_DBACCESS('<unlisted db>')
and received 1 as a result (in QA, while connected as a limited user to
another database - also unlisted).
Most (or all?) of the "unlisted" databases have been created by a script run
in QA and/or with osql.exe (command line parameters). Is there something I
am missing? My script assignes specific privilleges to a role that I
create, as well as to the "public" role.
The assignment of privilleges are done per the following script excerpt:
if not exists (select * from master.dbo.syslogins where loginname =
N'SomeUser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'master', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid <
16382)
EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
GO
exec sp_addrolemember N'db_datareader', N'SomeUser'
GO
exec sp_addrolemember N'db_datawriter', N'SomeUser'
GO
exec sp_addrolemember N'my_app_role', N'SomeUser'
GO
(You may recognize this as (based on) output from EM Generate SQL Script...)
I know I can use master..sysdatabases, but I am really trying to follow MS'
advice not to access the system tables directly...
While I found some similar questions through Google, I couldn't find any
that addressed this as an actual issue - apparently the "don't have access"
was more or less accepted without much protest...
Help, please...
Tore.Is the 'auto close' database option turned on? Apparently, closed database
are not included in this view.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tore" <pointzero@.vbdreamteam.net> wrote in message
news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> This is SQL Server 2000 with SP3a.
> When I do
> SELECT CATALOG_NAME
> FROM INFORMATION_SCHEMA.SCHEMATA
> I get only a subset of databases listed. I did check the permission to
> the non-listed databases, and without knowing exactly what permission
> "chain" the above view uses, the user definitely "has access" to the
> missing databases. I tried both with an account with very limited
> permissions (SQL Server login), "sa" (SQL Server login) and Integrated
> Security login from a Windows account in the (Local) Administrators group
> on the SQL Server machine. The login "BUILTIN\Administrators" has SA
> access, and has permissions to all the databases.
> If I connect as the same user to a different ("unlisted") database, the
> current database shows up in the above query, and the one that I was
> previously connected to (and therefore showed up) is gone from the
> results.
> I also tried
> SELECT HAS_DBACCESS('<unlisted db>')
> and received 1 as a result (in QA, while connected as a limited user to
> another database - also unlisted).
> Most (or all?) of the "unlisted" databases have been created by a script
> run in QA and/or with osql.exe (command line parameters). Is there
> something I am missing? My script assignes specific privilleges to a role
> that I create, as well as to the "public" role.
> The assignment of privilleges are done per the following script excerpt:
> if not exists (select * from master.dbo.syslogins where loginname =
> N'SomeUser')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'master', @.loginlang = N'us_english'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
> master.dbo.syslanguages where name = @.loginlang) and @.loginlang <>
> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
> END
> GO
> if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid
> < 16382)
> EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datareader', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datawriter', N'SomeUser'
> GO
> exec sp_addrolemember N'my_app_role', N'SomeUser'
> GO
> (You may recognize this as (based on) output from EM Generate SQL
> Script...)
> I know I can use master..sysdatabases, but I am really trying to follow
> MS' advice not to access the system tables directly...
> While I found some similar questions through Google, I couldn't find any
> that addressed this as an actual issue - apparently the "don't have
> access" was more or less accepted without much protest...
> Help, please...
> Tore.
>|||Thanks,
Yes, autoclose is on (which is a reasonable option for these databases).
I guess it's back to the system tables... :-<
Regards,
Tore.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OoS3%23ftRFHA.580@.TK2MSFTNGP15.phx.gbl...
> Is the 'auto close' database option turned on? Apparently, closed
> database are not included in this view.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tore" <pointzero@.vbdreamteam.net> wrote in message
> news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
The root cause of this is that the DATABASEPROPERTY() and
DATABASEPROPERTYEX() functions return NULL in cases where the database is
off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
expression on one of these functions; it returns null, and the so the row
is not seen in the result set.
This "Returns NULL" behavior of those functions is documented in the SQL
2005 BOL.
In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
change in SQL 2005. It now returns all the schemas in the current catalog
(database) rather than all catalogs (databases) in the server. This
changes brings our implementation into compliance with the SQL standard.
Sadly, our SQL 2000 implementation of that view was in error. This
breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
will detect it for you.
Unforutnately, the SQL standard does not provide as way to discover all
catalogs (databases) in the server. From the standard's perspective, that
is an "implementation detail." I can tell you that it is OK to use
master.dbo.sysdatabases in your SQL2K application and it will upgrade to
SQL 2005. NB: We have modified the security rules around who can "see" rows
in catalog views in SQL 2005. In this particular case, we've introduced a
new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed to
PUBLIC by default. The net result is that your application should continue
to work after you upgrade to SQL 2005.
IN general, you are OK to use system tables as long as you STAY AWAY FROM
UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
guarantees to support them in SQL 2005.
So, for example, avoid things like:
* sysproperties - undocumented
* sysxlogins - undocumented
* sysdatabases.mode - internal only
* sysdatabases.reserved - reserved
The rule of thumb is this:
If the system table has a topic page devoted to it in the BOL, it is OK.
If the column is documented and it does not say "internal" or "reserved",
it is OK.
Regards,
Cliff Dibble
Program Manager, SQL Server Engine
MSFT
Message posted via http://www.webservertalk.com|||Thanks for the detailed response. I find your hint about how to decide what
to stay away from especially useful.
However, the way I read the SQL Standard definition of
INFORMATION_SCHEMA.SCHEMATA is (quote):
"Identify the schemata that are owned by a given user."
Inspection of the underlying query also indicates that it refers to the
catalog that contains the information schema.
Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA,
it seems that INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?)
would be a prime candidate... :->
Thanks and Regards,
Tore.
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
> Hi,
> The root cause of this is that the DATABASEPROPERTY() and
> DATABASEPROPERTYEX() functions return NULL in cases where the database is
> off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
> expression on one of these functions; it returns null, and the so the row
> is not seen in the result set.
> This "Returns NULL" behavior of those functions is documented in the SQL
> 2005 BOL.
> In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
> change in SQL 2005. It now returns all the schemas in the current catalog
> (database) rather than all catalogs (databases) in the server. This
> changes brings our implementation into compliance with the SQL standard.
> Sadly, our SQL 2000 implementation of that view was in error. This
> breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
> will detect it for you.
> Unforutnately, the SQL standard does not provide as way to discover all
> catalogs (databases) in the server. From the standard's perspective, that
> is an "implementation detail." I can tell you that it is OK to use
> master.dbo.sysdatabases in your SQL2K application and it will upgrade to
> SQL 2005. NB: We have modified the security rules around who can "see"
> rows
> in catalog views in SQL 2005. In this particular case, we've introduced a
> new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed
> to
> PUBLIC by default. The net result is that your application should
> continue
> to work after you upgrade to SQL 2005.
> IN general, you are OK to use system tables as long as you STAY AWAY FROM
> UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
> guarantees to support them in SQL 2005.
> So, for example, avoid things like:
> * sysproperties - undocumented
> * sysxlogins - undocumented
> * sysdatabases.mode - internal only
> * sysdatabases.reserved - reserved
> The rule of thumb is this:
> If the system table has a topic page devoted to it in the BOL, it is OK.
> If the column is documented and it does not say "internal" or "reserved",
> it is OK.
> Regards,
> Cliff Dibble
> Program Manager, SQL Server Engine
> MSFT
> --
> Message posted via http://www.webservertalk.com|||> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA, it seems that

> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a prime ca
ndidate... :->
But that would make it proprietary, just the same as sysdatabases and the fu
ture sys.databases
are...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:Oo5FacUSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Thanks for the detailed response. I find your hint about how to decide wh
at to stay away from
> especially useful.
> However, the way I read the SQL Standard definition of INFORMATION_SCHEMA.
SCHEMATA is (quote):
> "Identify the schemata that are owned by a given user."
> Inspection of the underlying query also indicates that it refers to the ca
talog that contains the
> information schema.
> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA
, it seems that
> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a
prime candidate... :->
> Thanks and Regards,
> Tore.
>
> "Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in messa
ge
> news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
>|||Initially we were against adding anything new that was not explicit in the
standard. However, there does appear to be an escape clause in the
standard. See section 4.2, p.8 of Part 11.
"An SQL-implementation may define objects that are associated with
INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
implementation or any future version of ISO/IEC 9075 may also add columns
to tables that are defined in this Clause."
It's not entirely clear how we'd do that without risking some future
incompatibility with the standard. We'd probably have to define some MSFT-
specific naming convention.
In any case, we did not get around to adding I/S customizations in Yukon.
We will consider adding more extensions doing a fuller implementation of
the I/S views in the next product release. The I/S views encapsulate a lot
of JOINs required in the underlying catalog.
Regards,
Cliff Dibble
PM, SQL Engine
Message posted via http://www.webservertalk.com|||Just to close on the topic of system table compatibility in Yukon, here is
the complete list of previously documented columns that are no longer
compatible in Yukon, along with an explanation why. You should avoid
these, too.
view column_name value rationale
========================================
====================================
==
syscomments compressed 0 ctext is always uncompressed in Yukon.
sysdevices size 0 This has been deprecated since SQL2K
sysindexes statblob NULL Security reasons.
sysindexes keys NULL Severe perf hit. The format of the
binary
data was not documented.
sysobjects schema_ver 0 Due to architectural change, can not
guarantee strict monotonicity due to
round
off errors when converting/mapping from
8-byte datetime to 4-byte integer.
Use sys.objects.modify_date instead.
sysoledbusers rmtpassword NULL Security reasons.
sysremotelogins status 0 The format of the options was not
documented.
sysservers topologyy 0 The UI no longer uses this.
There are also a few incompatibilities in sysindexes (which are doc'ed in
the most recent SQL 2005 CTP builds):
a) No more indid=255 due to storage engine changes.
b) The page counters like "dpages" don't row-overflow.
c) The behavior of rowmodctr is not exactly the same due to storage engine
changes (we now carry per-column modification counters).
d) maxirow values are not identical due to storage engine changes.
e) keys column is NULL, as previously mentioned
f) The name column is NULL for heaps because we now have a unique index on
that column and we need to avoid possible name conflicts.
Finally - note that if you use NEW features in Yukon (e.g. partitioning or
any of the new user/schema DDL like CREATE USER), you will absolutely need
to migrate to the new catalog views. For example, sysindexes.root is NULL
for partitioned indexes because each partition has its own B-tree. Another
example:
select * from sysobjects where uid=1 and name = 'X'
might return >1 row due to user/schema separation (suppose dbo owned two
schemas S1 and S2, each of which contained a table named X).
Regards,
Cliff Dibble
PM SQL Engine
Message posted via http://www.webservertalk.com|||That's a good list, Clifford. Any plans to document it? (Or did I miss it in
April CTP BOL?)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:3d4abaedc650452998ebec042f092996@.SQ
webservertalk.com...
> Just to close on the topic of system table compatibility in Yukon, here is
> the complete list of previously documented columns that are no longer
> compatible in Yukon, along with an explanation why. You should avoid
> these, too.
> view column_name value rationale
> ========================================
==================================
====
> syscomments compressed 0 ctext is always uncompressed in Yukon.
> sysdevices size 0 This has been deprecated since SQL2K
> sysindexes statblob NULL Security reasons.
> sysindexes keys NULL Severe perf hit. The format of the
> binary
> data was not documented.
> sysobjects schema_ver 0 Due to architectural change, can not
> guarantee strict monotonicity due to
> round
> off errors when converting/mapping from
> 8-byte datetime to 4-byte integer.
> Use sys.objects.modify_date instead.
> sysoledbusers rmtpassword NULL Security reasons.
> sysremotelogins status 0 The format of the options was not
> documented.
> sysservers topologyy 0 The UI no longer uses this.
> There are also a few incompatibilities in sysindexes (which are doc'ed in
> the most recent SQL 2005 CTP builds):
> a) No more indid=255 due to storage engine changes.
> b) The page counters like "dpages" don't row-overflow.
> c) The behavior of rowmodctr is not exactly the same due to storage engine
> changes (we now carry per-column modification counters).
> d) maxirow values are not identical due to storage engine changes.
> e) keys column is NULL, as previously mentioned
> f) The name column is NULL for heaps because we now have a unique index on
> that column and we need to avoid possible name conflicts.
>
> Finally - note that if you use NEW features in Yukon (e.g. partitioning or
> any of the new user/schema DDL like CREATE USER), you will absolutely need
> to migrate to the new catalog views. For example, sysindexes.root is NUL
L
> for partitioned indexes because each partition has its own B-tree. Anothe
r
> example:
> select * from sysobjects where uid=1 and name = 'X'
> might return >1 row due to user/schema separation (suppose dbo owned two
> schemas S1 and S2, each of which contained a table named X).
> Regards,
> Cliff Dibble
> PM SQL Engine
> --
> Message posted via http://www.webservertalk.com|||> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
My .02: Don't.
I think can see the temptation: "By just adding this column, the view will b
e usable in a lot more
scenarios."
To me, the purpose of these views are that you know you are within the stand
ard. If I would need
something outside these views, I prefer diving into the catalog views or sys
tem tables, as I would
still need a re-write in the unlikely case I need to port the app. Also, the
catalog views have a
much better design than the old style system tables, so ease of use (info sc
hema vs. system tables)
isn't an argument either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8ee5d4d5a35947ec98ee43006ed152cd@.SQ
webservertalk.com...
> Initially we were against adding anything new that was not explicit in the
> standard. However, there does appear to be an escape clause in the
> standard. See section 4.2, p.8 of Part 11.
> "An SQL-implementation may define objects that are associated with
> INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
> implementation or any future version of ISO/IEC 9075 may also add columns
> to tables that are defined in this Clause."
> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
> In any case, we did not get around to adding I/S customizations in Yukon.
> We will consider adding more extensions doing a fuller implementation of
> the I/S views in the next product release. The I/S views encapsulate a lo
t
> of JOINs required in the underlying catalog.
> Regards,
> Cliff Dibble
> PM, SQL Engine
> --
> Message posted via http://www.webservertalk.com

Thursday, March 22, 2012

bugs, Information_schema appeared as a user in master database

bugs, Information_schema appeared as a user in master database

bugs, Information_schema appeared as a user in master database

Thursday, March 8, 2012

Bug in INFORMATION_SCHEMA?

Bug in INFORMATION_SCHEMA.SCHEMATA?

Boot.ini file

Blog Archive

About Me