June 9, 2005 at 7:08 am
Hello all,
is there any way to get a list of the name of the database owner, if the database has any active backups, replication set and any replication active, for all the databases on a sql server?
tia
Hans
June 9, 2005 at 4:20 pm
Hi,
here is a possible script for the owner list. You may query the last backup from MSDB, I have to go now, email me tomorrow I will send you. Pay attention that it is 2 single quotes around ? in the query, not a double quote
sp_MSforeachdb
' select ''?''
select name from master.dbo.syslogins
where SID =
(Select SID from sysusers
where name = ''DBO'')'
Yelena
Regards,Yelena Varsha
June 10, 2005 at 3:34 am
Thank you for responding Yelena.
I tried this, but I get no records as a result from this. When I check for the owner of a database by checking the Properties is can see a name, e.g. BUILDING/Abcd, BUILDING beinf the domain name and Abcd the Windows login.
Is there a way to get this information by using tsql, or do I have use DMO for this?
Hans
June 10, 2005 at 12:30 pm
This one may work better
sp_MSforeachdb
' select ''?''
select name from master.dbo.syslogins
where SID =
(Select SID from ?.dbo.sysusers
where name = ''DBO'')'
The difference is in ?.dbo before sysusers. I did not have an opportunity to validate possible test cases for the script yesterday, all my DBs on the server have its owner SA, so it looked like worked for me yesterday, today I connected to another machine with different DBowners and corrected the script. Yes, this is t-sql. You don't have to use SQL-DMO but may be able to use VBscript with this query and put it on schedule or let it to send you an email every day.
For backup statuses I will send you another message
Yelena
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply