Finding a creator of object

Hi all,

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo

create proc dbo.test
as
print 'hello'

Is there any place where SQL server keeps the record of creator?shiju (shiju.samuel@.gmail.com) writes:

Quote:

Originally Posted by

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo
>
create proc dbo.test
as
print 'hello'
>
Is there any place where SQL server keeps the record of creator?

No. You would have to have trace running that captutes the Object:Created
event. In SQL 2005 you could also set up a DLL trigger.

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

Find object owner

Is there a query I can write that joins 2 system tables to return the owners of objects (e.g. tables) and the object's name?

Thanks,

Dave

IN SQL SERVER 200 the SCHEMA IN the information_VIEW represents the owner OF an objects e.g. FOR tables

SELECT TABLE_SCHEMA FROM [INFORMATION_SCHEMA].TABLES

IN SQL SERVER 2005, you will have TO determine the owner OF the SCHEMA instead using

SELECT Name FROM sys.Schemas

INNER JOIN sys.server_principals

ON [Schemas].principal_id = [server_principals].Principal_id

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

In SQL SERVER 2005, because of the ALTER AUTHORIZATION clause, the schema owner may not always be the object owner

See, http://msdn2.microsoft.com/en-us/library/ms187359.aspx

So, an alternative to the above query for SQL SERVER 2005 is

select user_name(objectproperty(object_id,'OwnerId')), name from sys.objects

This always returns the correct owner of the object and takes 'ALTER AUTHORIZATION' into account.

find locks in a database by using sp_lock

I’m trying to find locks in a database by using sp_lock. However the sp_h
elp
give me an object name as a number. Does anyone have any script which
provide the tables name itself.
Best regards,
Ron
sp_lock to get the object number and then to translate the number to table
name.
SELECT TABLE_CATALOG, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(85575343)See if this helps: http://vyaskn.tripod.com/code.htm#sp_lock2
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:26AFF5F6-E19A-4D6D-AD8D-D61774DC9B13@.microsoft.com...
> I'm trying to find locks in a database by using sp_lock. However the
sp_help
> give me an object name as a number. Does anyone have any script which
> provide the tables name itself.
> Best regards,
> Ron
> sp_lock to get the object number and then to translate the number to table
> name.
> SELECT TABLE_CATALOG, TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = OBJECT_NAME(85575343)
>|||If you use the below script, make sure you follow Vyas's directions on his
webpage -- i.e. run it in the database whose locks you are interested in.
If you run it from a different database the script is likely to display the
wrong object names. This is because the object_name function operates in
the context of the current database while sp_lock displays locks from all
databases. Thus if you have locks on tables DB1.dbo.Foo and DB2.dbo.Bar and
lets say that these two tables happen to have the same object id in their
respective databases, then if you run sp_lock2 from DB1 it will display Foo
for both locks; if you run it from DB2 it will display Bar for both locks;
and if you run it from master it will display the name of whatever table in
master has the corresponding object id, or null if master does not have a
table with that id.
Santeri (Santtu) Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uNl$cVyXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/code.htm#sp_lock2
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:26AFF5F6-E19A-4D6D-AD8D-D61774DC9B13@.microsoft.com...
> sp_help
>

Find column name and datatype of a given table

Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-SudhakarHi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||Thank you..

using the following query lists only the columns that are acessible to the user. If the user does not have permission on a column it will not be displayed. But I want a query to display all column names irrespective of the user has any permissions to modify or select.

Thanks
-Sudhakar


Hi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||You can construct a query on the syscolumns and sysobjects tables - these are not dependent on the user permissions.