Modification Logs

Is there a way to determine when a file was changed/modified? We're on
SQL 2000 and I need to know when a view was modified and by whom.
Thanks!By "file" I assume you mean that the view on the database has been
modified?

If so then there are several things you could do:

1. Get a transaction log examining tool which will let you scan the
transaction logs for the DDL command that modified the view. The "by
whom" depends on how your database security is set up. If, for
example, everyone is accustomed to using the "sa" account then this
won't tell you very much. If you have specific account set up for
each individual user then you'll have all the info you need.

2. If the answer to the above was the former then review your database
access security and ensure that only person-specific user accounts
have the privileges to make modifications.

3. Implement a change process for your SQL code - take a look at
www.dbghost.com for a tool that enables such a process.sql

Mixed Mode vs. Windows Authentication

I am trying to create a query that can determine if a user id is using mixed mode/windows/both authentication. I need to do this so that it can run on both sql server 2000 and 2005, meaning I can't use any of the sys.* views. Is there a single query could use for both systems?
-Kyle

Hi kschlap,

there is no method for your issue.

but maybe you can try to build a view to select cross these 2 servers.

create view v_loginid_info

as

select 'servname'='mssql2k', loginid

from mssql2k.master.dbo.sysprocesses

union all

select 'servname'='mssql2k05', loginid

from mssql2k05.master.sys.sysprocess

try to think about.

hoping this can help you.

Best Regrads,

Hunt.

|||I have found the query...

select name, is_policy_checked
from sys.sql_logins

When I use this query, it doesn't pick up the windows authenticated users. Is there a way to get it to pick up all users?
-Kyle|||

How about something like this:

select name, isntuser from syslogins

isntuser=1 means Windows authentication

isntuser=0 means SQL Server authentication

Ben

sql