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),''IsMSShipp ed'') = 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
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),''IsMSShipp ed'') = 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),''IsMSShipp ed'') = 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...[vbcol=seagreen]
> 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),''IsMSShipp ed'') = 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:
|||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:[vbcol=seagreen]
> 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, 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.googlegr oups.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
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/is...y/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.googlegr oups.com...
>
>

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 gro
up
> 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 d
b
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the proble
m?
> 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...[vbcol=seagreen]
> 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:
>|||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:[vbcol=seagreen]
> 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 sq
l
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and hav
e
> 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, 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
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
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
John
"Yifei" wrote:

> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_she
ma
> 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...
>
>sql

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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

buggy sql like command with wildcards to xml

I have this stored procedure, witch works nice under the query mode
create procedure MKR
@.mkrvar nvarchar(50)
AS
SELECT *
FROM QUADRA
WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
GO
so i call it like this on my browser :
http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
is not working...oh !
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
LIKE '%57%')FOR XML NESTED&root=root
it doesnt work too
so i trought that might be something buggy...donno...so i tried this
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
XML RAW&root=root
ok...it doesnt work...why ?
but this work
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR XML
RAW&root=root
so...why i cant use wildcards to generate an xml ?
i need a fix or a workarround for this asap, if someone could help :D
thanks
max
% is an escape character in a URL. For example you will see spaces
converted to %20.
Try %%57%% instead.
"Antonio Max" <maxspam@.bol.com.br> wrote in message
news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
>I have this stored procedure, witch works nice under the query mode
> create procedure MKR
> @.mkrvar nvarchar(50)
> AS
> SELECT *
> FROM QUADRA
> WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
> GO
> so i call it like this on my browser :
> http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
> is not working...oh !
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
> LIKE '%57%')FOR XML NESTED&root=root
> it doesnt work too
> so i trought that might be something buggy...donno...so i tried this
>
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
> XML RAW&root=root
> ok...it doesnt work...why ?
>
> but this work
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR
> XML
> RAW&root=root
> so...why i cant use wildcards to generate an xml ?
> i need a fix or a workarround for this asap, if someone could help :D
> thanks
> max
>
>
|||thanks a lot
forgot this;;.. eerr
anyway...it looks like this now
http://mywebsite/?sql=SELECT%20*%20F...DRA%20WHERE%20(QUADRA%20LIKE%20
'%2557%25')%20FOR%20XML%20RAW&root=root
where %25 is the % sign on url encoding char table
thanks
max
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:#T#EIIzKEHA.892@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> % is an escape character in a URL. For example you will see spaces
> converted to %20.
> Try %%57%% instead.
>
> "Antonio Max" <maxspam@.bol.com.br> wrote in message
> news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
9)[vbcol=seagreen]
FOR
>

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why??
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny Tordrup
Benny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
David G.
|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>
|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
>

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why''
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny TordrupBenny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
--
David G.|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
--
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > Hi
> >
> > I have a table defined as
> >
> > create table Project
> > (
> > Project nvarchar(20) collate database_default not null constraint
> > DF_Project_Project default '',
> > /* some other irrelevant fields */
> >
> > CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> > )
> >
> > If I insert a long numeric value into Project (3123456789), I get some
> > troubles:
> >
> > insert Project (Project) values ('3123456789')
> >
> >
> > If i execute the statement
> >
> > select Project from Project
> >
> > in Query Analyzer, I get the expected result (the record shown in the
> > result
> > grid)
> >
> > If i execute the statement
> >
> > select * from Project
> >
> > I get the following error (and no record shown in the result grid)
> >
> > Server: Msg 248, Level 16, State 1, Line 1
> > The conversion of the nvarchar value '3123456789' overflowed an int
> > column.
> > Maximum integer value exceeded.
> >
> >
> > Why''
> >
> > My SQL Server version is (according to Select @.@.Version)
> >
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > IMO, using Select * should be functionally equal to a Select statement
> > listing all fields in the table, but it is not.
> >
> > Best regards,
> >
> > Benny Tordrup
> >
> >
>