Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Tracking access to sqlserver

Author  Topic 

farrokhp
Starting Member

15 Posts

Posted - 2011-03-03 : 03:56:01
in sql 2005 is there anyway, I want to see for example who still have admin access to sql databases? who means any windows users or any sql authentication user? can you give me a script to list these users? also need a script to show who login to which databse and who logged in as sa in last 24 hours.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2011-03-04 : 06:13:56
exec sp_helpsrvrolemember 'sysadmin'
exec sp_helpsrvrolemember 'securityadmin'
also check who has db_owner role, in each database: sp_helprolemember

To check the logins for the past 24 hours, you can check the default trace file (if you have not disabled it).
Query the system table to check: select * from sys.traces
Go to Top of Page
   

- Advertisement -