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 out owner of database for SQL 2000 and 2005 databases

Hello,
As the title says I need to find out the owner of database on both sql 2000 and 2005. I found a query which works great on sql 2005..

select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'

But cannot run this in QA against a sql 2000 database -- "Invalid object name 'sys.databases'."

Anyone know how I can accomplish this?

Select * from master.dbo.sysdatabases doesn't give me any promising data either,

BI was almost there...for anyone looking to do this you need to change a couple of clauses from the 2005 query i posted. Firstly SELECT *
FROM sys.databases -> SELECT * FROM master.dbo.sysdatabases and the column owner_sid needs to be changed to its 2000 equivalent of just sid...

All in all

2005: select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'

2000: select suser_sname(sid) from master.dbo.sysdatabases where name = 'DatabaseName'

B

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.