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 permissions

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
An sa ID for the master sees everything however.
Thanks for your help
PachydermitisI believe your user needs to be in the ddl_admin role?
"Pachydermitis" <dedejavu@.hotmail.com> wrote in message
news:4f954dcf.0309190903.6fbdfc77@.posting.google.com...
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis

information_schema permissions

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
An sa ID for the master sees everything however.
Thanks for your help
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.

Don't really see where INFORMATION_SCHEMA comes in. There is no
information about indexes there. Generally, I prefer using the
system tables, because they hold the complete set of information.

To see all indexes in a database (save those on system tables):

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||dedejavu@.hotmail.com (Pachydermitis) wrote in message news:<4f954dcf.0309190902.1d421bf4@.posting.google.com>...
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis

Hi,

This would give you the info what you are seeking..

select name, object_name(id) from sysindexes

-Manoj|||Manoj Rajshekar (manrajshekar@.yahoo.com) writes:
> This would give you the info what you are seeking..
> select name, object_name(id) from sysindexes

And a lot more he is not interested in. He will also get the names of
heap tables (tables without clustered indexes), hypothetcial indexes,
statistics and location of text data. This SELECT filters this kind
of information:

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

and also filters indexes on system tables.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
I have been able to get the TableName and IndexNames (along with a few
I don't want _WA_. . .) but I can't seem to get the column names or
get rid of the _WA_ ones.

I was trying to get TableName, IndexName, ColumnName

SELECT obj.[name],ind.[name] FROM sysindexes ind
inner join sysobjects obj on ind.[id]=obj.[id]
ORDER BY obj.[name],ind.indid

Thanks again
Pachydermitis|||Pachydermitis (dedejavu@.hotmail.com) writes:
> I have been able to get the TableName and IndexNames (along with a few
> I don't want _WA_. . .) but I can't seem to get the column names or
> get rid of the _WA_ ones.

Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)

> I was trying to get TableName, IndexName, ColumnName

Here is a query that gives this. For multi-column indexes you get one
row per index. If you want all columns for an index on one line, you
will have run some iteration.

SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

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

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

INFORMATION_SCHEMA again

Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
use tempdb
go
if object_id('temp_proc') is not null drop proc temp_proc
go
create proc temp_proc
as
SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
go
exec tempdb.dbo.temp_proc
use master
exec tempdb.dbo.temp_procThe procedure is still executed in the context of tempdb, not of master. I
think it would be very unexpected behavior if you changed information_schema
views to real user tables, and calling pubs.dbo.someprocedure from tempdb,
tried to find an authors table in tempdb?
A
"Jay" <nospan@.nospam.org> wrote in message
news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
> use tempdb
> go
> if object_id('temp_proc') is not null drop proc temp_proc
> go
> create proc temp_proc
> as
> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
> FROM INFORMATION_SCHEMA.TABLES
> go
> exec tempdb.dbo.temp_proc
> use master
> exec tempdb.dbo.temp_proc
>|||Huh?
Are you saying that a procedure will always execute within the database it
was created in?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> The procedure is still executed in the context of tempdb, not of master.
> I think it would be very unexpected behavior if you changed
> information_schema views to real user tables, and calling
> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
> tempdb?
> A
>
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>|||Would something else make more sense?
USE Pubs;
GO
CREATE PROCEDURE dbo.GetAuthors
AS
SELECT * FROM Authors;
GO
USE master;
GO
EXEC Pubs.dbo.GetAuthors;
You expect this to return rows from master.dbo.Authors?
"Jay" <nospan@.nospam.org> wrote in message
news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
> Huh?
> Are you saying that a procedure will always execute within the database it
> was created in?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>|||I expected to be able to write a general purpose procedure that would detect
the catalog of the database it was executed in. In the example you gave, I
would expect it to complain that there was no Authors table, not go back to
the database the procedure was created in and read the Authors table in
there while the specified database context was master.
However, looking back at the nightly maintenance procedure I wrote, do do
this I have to get cute with dynamic sql and embeded 'use' statemants.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23NBYCrCIHA.4472@.TK2MSFTNGP05.phx.gbl...
> Would something else make more sense?
>
> USE Pubs;
> GO
> CREATE PROCEDURE dbo.GetAuthors
> AS
> SELECT * FROM Authors;
> GO
> USE master;
> GO
> EXEC Pubs.dbo.GetAuthors;
>
> You expect this to return rows from master.dbo.Authors?
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> Huh?
>> Are you saying that a procedure will always execute within the database
>> it was created in?
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>>
>

Information_schema ?

Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
ThanksThese are system views set up in the master database.

They can be used as templates for other databases to get meta data info. Like table, constraint and view info.

All you do is copy them accross into query anyalyzer and create them on your user database.

Cheers