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

INFORMATION_SCHEMA: Probably bug in SQL-2005

Try to run this script, first in any database under SQL-2000, then in SQL-2005:

select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT IS NOT NULL

In SQL-2000 values of the defaults are in single pair of brackets:

COLUMN_DEFAULT

('')
(0)
('')
(0)
(0)
(getdate())
(db_name())
(1)
(21)
(N'anonymous')

In SQL-2005 integer values are in double brackets, and all other defaults - in single brackets:

COLUMN_DEFAULT

('')
((0))
('')
((0))
((0))
(getdate())
(db_name())
((1))
((21))
(N'anonymous')

At the same time, OpenSchema method of ADO:

Connection.OpenSchema(adSchemaColumns)

returns different defaults in SQL2000 and SQL2005. In SQL2000 column defaults are in useful form, without any brackets:

'', 0, getdate()

But in SQL2005 they are enclosed in single brackets:

(''), (0), (getdate())

Does anybody know this problem? Is it normal, or it's a bug?

SQL Server 2005 normalizes expressions in defaults, check constraints and computed columns. As a result of this, the expression will look different from how it was stored in SQL Server 2000. However a particular expression will look the same irrespective of how you write it (extra spaces, paranthesis etc). This is documented in the following BOL topic:

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

|||

Now I understand!

Thanks!

Information needed on jobs !

Hi
I please need your help on jobs. I am new to it !
For all our world wide customers, I need to have a sql
script that will create a job to be run on each production
database. This will just call a stored procedure that
will update data in that DB.
1) what minimum permissions or roles are needed to create
a job ?
2) NB NB:: can a job be scheduled to run based on the
results of a sql stamenent ?
E.g. run every getdate() + (select no_of_days from .....)
Your help is appreciated immensely !!
Thanks
Tania1. Anybody in public role can create a job.
2. I am not sure if I understood this correctly or not. You can write a
cursor/loop to iterate through each rows in your resultset and call the
sp_add_jobschedule/sp_update_jobschedule procedures. If you are asking about
running a job, you can use the sp_start_job stored procedure. All details
regarding these procedures can be found in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )

INFOMRATION_SCHEMA.COLUMNS question

I have a script that is looking at
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Security_Code_DE'
AND COLUMN_NAME = 'ActiveProductBitMap')
I can see in enterprise manager that the db I restored does not have a
column ActiveProductBitMap, nor should it. The db before restore did. I must
have taken some sequence of steps to fail to update the master db. But I
don't know what that would be, since I know very little about the master db.
How does the master db keep track of schema on restore? ThanksI'm not sure what you asking here...

> I can see in enterprise manager that the db I restored does not have a
> column ActiveProductBitMap, nor should it.
... OK.

> The db before restore did.
Which database? The one you just restore onto? If so, that is overwritten by
a restore operation.
I fail to see what the master database has to do this this. Can you elaborat
e?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:erbEsWLqFHA.3520@.tk2msftngp13.phx
.gbl...
>I have a script that is looking at
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> I can see in enterprise manager that the db I restored does not have a
> column ActiveProductBitMap, nor should it. The db before restore did. I mu
st
> have taken some sequence of steps to fail to update the master db. But I
> don't know what that would be, since I know very little about the master d
b.
> How does the master db keep track of schema on restore? Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23ix%23jLqFHA.1324@.tk2msftngp13.phx.gbl...
> I'm not sure what you asking here...
>
> ... OK.
>
> Which database? The one you just restore onto? If so, that is overwritten
> by a restore operation.
>
Yes.

> I fail to see what the master database has to do this this. Can you
> elaborate?
These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the
db. The db guy here is telling me that during a restore SQL server updates
the information in master to reflect the schema of the db as restored. This
is not what I am seeing. Since scripts that run are accessing information in
the INFORMATION_SCHEMA views, the underlying data about the schema should
reflect the schema as it currently exists in the db.
So, is the db guy wrong, and the restore process does not update the schema
info stored in master? Or is there a proper way to do the restore such that
the schema info is reflected properly?
Thanks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Stephanie" <IwishICould@.NoWay.com> wrote in message
> news:erbEsWLqFHA.3520@.tk2msftngp13.phx.gbl...|||> These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the db. The db guy
here is
> telling me that during a restore SQL server updates the information in mas
ter to reflect the
> schema of the db as restored.
That makes no sense at all to me. Seems to me like that person doesn't know
SQL Server very well or
that the person didn't explain very well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%239UvRsLqFHA.3136@.TK2MSFTNGP11.p
hx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23%23ix%23jLqFHA.1324@.tk2msftngp13.phx.gbl...
> Yes.
>
> These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the
db. The db guy here is
> telling me that during a restore SQL server updates the information in mas
ter to reflect the
> schema of the db as restored. This is not what I am seeing. Since scripts
that run are accessing
> information in the INFORMATION_SCHEMA views, the underlying data about the
schema should reflect
> the schema as it currently exists in the db.
> So, is the db guy wrong, and the restore process does not update the schem
a info stored in master?
> Or is there a proper way to do the restore such that the schema info is re
flected properly?
> Thanks.
>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eA23TqMqFHA.2416@.TK2MSFTNGP14.phx.gbl...
> That makes no sense at all to me. Seems to me like that person doesn't
> know SQL Server very well or that the person didn't explain very well.
>
How does one get the master database to reflect the actual schema of a
restored db? I, for one, don't know the internals of Sql Server At All. And
I am having trouble finding anything other than transact sql reference to
this information. Or am I missing the point of these views?
Thanks.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Stephanie" <IwishICould@.NoWay.com> wrote in message
> news:%239UvRsLqFHA.3136@.TK2MSFTNGP11.phx.gbl...
>|||I don't know what schema you refer to. The database schema is contained *ins
ide* the database, not
in master.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%23MbBC0MqFHA.2696@.TK2MSFTNGP11.p
hx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eA23TqMqFHA.2416@.TK2MSFTNGP14.phx.gbl...
> How does one get the master database to reflect the actual schema of a res
tored db? I, for one,
> don't know the internals of Sql Server At All. And I am having trouble fin
ding anything other than
> transact sql reference to this information. Or am I missing the point of t
hese views?
> Thanks.
>
>|||> How does one get the master database to reflect the actual schema of a
> restored db?
The master database knows very little about the schema of user databases.
These are stored in the user databases and, in the case of a database
restore, the backup file contains this information, not master. This is why
you can backup a database on one server, and restore it on another (whose
master database, obviously, has absolutely no knowledge of the new
database).
So perhaps, instead of trying to "get the master database to reflect"
anything, you need to describe exactly what you are trying to accomplish.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23kw704MqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> The master database knows very little about the schema of user databases.
> These are stored in the user databases and, in the case of a database
> restore, the backup file contains this information, not master. This is
> why you can backup a database on one server, and restore it on another
> (whose master database, obviously, has absolutely no knowledge of the new
> database).
> So perhaps, instead of trying to "get the master database to reflect"
> anything, you need to describe exactly what you are trying to accomplish.
> A
>
I am looking at a script with
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Security_Code_DE'
AND COLUMN_NAME = 'ActiveProductBitMap')
This condition is returning true. If I do select * from
information_schema.columns... I see a row. If I right click on the table in
Enterprise manager, and select design, I do not see this column. This is a
problem for me.|||> information_schema.columns... I see a row. If I right click on the table
> in Enterprise manager, and select design, I do not see this column. This
> is a problem for me.
And what do you see when you open Query Analyzer, hit F8, go down to the
table, expand, and expand the Columns node? Is your phantom column there
are not?|||> I am looking at a script with
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> This condition is returning true. If I do select * from information_schema
.columns... I see a row.
> If I right click on the table in Enterprise manager, and select design, I
do not see this column.
> This is a problem for me.
Perhaps it is only a matter of refresh in EM? Or could you have two tables w
ith the same name but
different owners?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%23hJBdkNqFHA.3724@.TK2MSFTNGP12.p
hx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23kw704MqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I am looking at a script with
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> This condition is returning true. If I do select * from information_schema
.columns... I see a row.
> If I right click on the table in Enterprise manager, and select design, I
do not see this column.
> This is a problem for me.
>

Indexing a selection query

Hey,

I've started a new topic for this, as the old was is a different problem, lol.

This script is working now, however it says the unitIndex is 287 for all units.

What I wanted the unitIndex to be is, 1 for the unit with highest tsr, and (if 30 units) 30 for the unit with lowest tsr.

Why is it making the UnitIndex 287, and how can I fix it? lol

(I have marked the line where the unitIndex is

PROCEDURE units_display (@.startIndex INT, @.endIndex INT)
declare @.maxrts bigint;
select @.maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from unit);
if @.maxrts = 0
set @.maxrts = 1;
declare @.maxValue bigint;
set @.maxValue=2500
select *
from (select *, (select unit from UnitTypes where UnitTypes.unitid = unit.UnitType) as unitTypeName, ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs2 from Unit) as Unit1
inner join
(select Unit3.trs, count(*) as UnitIndex --< here is UnitIndex
from
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs from Unit) as unit3
inner join
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs3 from Unit) as Unit2 on Unit3.trs <= Unit2.trs3
group by Unit3.trs) as UnitIndexes
on Unit1.trs2 = UnitIndexes.trs
where UnitIndexes.UnitIndex between @.StartIndex and @.EndIndex
order by Unit1.trs2 DESC

ThanksIt seems like you are making this more complicated than it should be. What are the table definitions and what are you trying to do? Is tsr a calculated field?

To order the records and determine a unit index for them, I would create a subquery in the select clause that counts all records that have a greater tsr.|||Hey,

yea, if I can simplify this, that's be awesome.

I am only looking at one table, I want to select all the fields from it, but relevant fields are:

UNIT
unitid int pk
Name vc(50)
games int
wins int
unittype int (fk referencing UnitTypes, unitid)

UNITTYPES
unitid int PK
unit varchar(20)

tsr is a column I am working out on the fly, it isn't stored in the database. perhaps a better method would be to create a view first with tsr in it, then run the reorder etc after, however I am unsure how views work, and as of yet, I havn't had time to look into them to determine if they are what I should use.

Any help simplifying this would be awesome, lol.

I have solved the issue I had with the script below, however if you can should me however, it is just a repair on the code as it is there, lol

I'll post the repair in here if you'd like to work from that. Basically though, once I determine the column tsr, I then want to determine the 'ranks' of each unit. So basically, units with the same tsr score, get the worse rank, ie, the third and fourth highest scores both get rank 4 (rank = unitindex form the script below). once that is done, I then was to assign a position variable to each row.

I am doing the same thing with a similar query, which a few people are discussing with me in another post. From what they have said, I will be looking at creating a table, with the columns i desire, plus am incrementing 'position' column.

I will then use something like

insert into @.tempTable (the select statement which returns a reordered table with the tsr value).

Thanks
-Ashleigh

-Ashleigh

Indexes on Bulk Insert data

Any help would be appreciated.

I am running a script that does the following in succession.

1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures

I would like to improve the performance of the analysis in step 4 by
creating indexes in step 2.

Question 1-Are indexes updated when data is bulk inserted? I know they are
when using normal insert, update, or delete T-SQL but I am not sure about
bulk insert of data.

Question 2-Do I need to update the index statistics in any way or would they
be ready to use in step 4.

Thanks,
CJI would define step 4 as create indexes, that will have your stats up to
date and save you from any performance issues during the load or having
to reindex or update the stats

I would do the analysis in step five (depending what type of analysis)

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Chris (chris@.hrn.org) writes:
> 1-Drop existing database and create new database
> 2-Defines tables, stored procedures and functions in the database
> 3-Imports data using bulk insert
> 4-Analyzes data using stored procedures
> I would like to improve the performance of the analysis in step 4 by
> creating indexes in step 2.
> Question 1-Are indexes updated when data is bulk inserted? I know they are
> when using normal insert, update, or delete T-SQL but I am not sure about
> bulk insert of data.

Yes, they are. However, you may prefer to wait with creating indexes until
you have loaded the data for best performance. You may also opt to create
clustered indexes before bulk-loading and add non-clustered indexes after.
This is particularly appealing if the order in the data files corre-
sponds to the clustered indexes.

> Question 2-Do I need to update the index statistics in any way or would
> they be ready to use in step 4.

If you create indexes after bulk-loading, SQL Server will create statistics
for you when creating the indexes.

If you create indexex before bulk-loading, the statistics will not be
correct after the load. Thus, it can be a good idea run UPDATE STATISTICS
in this situation. However, if you don't, SQL Server will auto-update
statistics, unless you have turned off this feature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

indexes

where can i find a example on how to
build a script to reindex all my tables
Tks
DavePDaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

where can i find a example on how to
build a script to reindex all my tables

http://www.google.se/search?hl=sv&q...=S%C3%B6k&meta=
You could also look in Books Online for the definition of sysindexes and sysobjects if you are on SQL 2000 or sys.objects and sys.indexes if you
are on SQL 2005.

One thing to be careful with is that if fragmentation is below 30%, there is
not so much to gain with reindexing, so adding some intelligence to handle
this may be worth it. I don't have any example at hand, but the hits on
Google may give you something.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thank you much erland
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99AAF398D854AYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:
>

Quote:

Originally Posted by

>where can i find a example on how to
>build a script to reindex all my tables

>
http://www.google.se/search?hl=sv&q...=S%C3%B6k&meta=
>
You could also look in Books Online for the definition of sysindexes and
sysobjects if you are on SQL 2000 or sys.objects and sys.indexes if you
are on SQL 2005.
>
One thing to be careful with is that if fragmentation is below 30%, there
is
not so much to gain with reindexing, so adding some intelligence to handle
this may be worth it. I don't have any example at hand, but the hits on
Google may give you something.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx