authorization

how do you alter authorization to set the database owner to a valid login? and simultaneously how do you add the database diagram support objects in sql server 2005 express.

thanksThere's two ways to change the owner to a valid login:

1) Using the management UI
a) Right click on the database in Object Explorer, select properties
b) in the properties dialog, switch to the "Files" page
c) Enter the name of a login in the "Owner" edit box (or click the "..." button to browse for a login)
d) click OK

2) Using T-SQL execute the following:

ALTER AUTHORIZATION ON DATABASE::{database_name} TO {principal_name}
To install the database diagram support objects:

1) Launch Management Studio and log in as an administrator or as dbo in the database.
2) Make sure the database compatibility level is set to SQL Server 2005. You can set this in the database properties dialog on the Options page.
3) In Object Explorer, right click on the "Database Diagrams" folder for the database and select "Install Diagram Support". Click Yes in the prompt to install support objects that is displayed.

If you need to, you can set the database compatibility to a backwards compatibility mode after the diagram support objects are installed and you'll still be able to work with diagrams.

Auditing without having to use SQL Profiler

I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.

Auditing SQL Server users

I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.Not sure if that can be achieved. Wh don't you run an audit trace|||That's what Profiler is for. In fact, if properly scripted, it can gather whatever info you want and store it in a database, so you don't need to keep it in a text file. And your email notification can be limited to just something like "A new entry was added to the AUDIT table for USER_CREATE event." You can create a trigger on that AUDIT table and have either SQLMail or CDO/XPSMTP send you this short notice. And then you can decide whether to react upon it or let it go.