Information_Shema as a user in Master database

Hi, All
Recently, I was running a script 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, All,
I think this is an issue for sql 2005, because the when I open em from db
server, the information_schema user does not show, but I connect it through
a remote machine that installed sql 2005, and the user showed.
same as another machine that did not run the script and did installed sql
2005.
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:uVLA%23HI$FHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, All
> Recently, I was running a script 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
>
>

Indexing ?

I have a simple table that includes an ID and 8 user-defined data fields. Th
e
fields are nvarchar(255) and the application allows the user to store any
data they like in them. The application also allows searching on any
combination of up to 5 of the fields, with an AND or OR logical combination.
The query returns the result set unordered.
Does anyone have any thoughts on an indexing scheme for this situation? I'm
not quite sure what is best given that the user may search on any combinatio
n
of fields. Some obvious thoughts are:
1. An separate index on each column.
2. A single index covering all columns.
3. A separate index on each of the 8 choose 5 combinations of columns
(whoah!).
Any thoughts?Although I do not condone this, creating an index on each of the columns
would allow SQL Server to use Index Joining / Intersection to perform the
search. Creating an Index on all the columns would only benefit the
statements that search on the columns in the order of the index.
"Ken" wrote:

> I have a simple table that includes an ID and 8 user-defined data fields.
The
> fields are nvarchar(255) and the application allows the user to store any
> data they like in them. The application also allows searching on any
> combination of up to 5 of the fields, with an AND or OR logical combinatio
n.
> The query returns the result set unordered.
> Does anyone have any thoughts on an indexing scheme for this situation? I'
m
> not quite sure what is best given that the user may search on any combinat
ion
> of fields. Some obvious thoughts are:
> 1. An separate index on each column.
> 2. A single index covering all columns.
> 3. A separate index on each of the 8 choose 5 combinations of columns
> (whoah!).
> Any thoughts?
>