April 17, 2012 at 11:57 am
Hi Guys,
As a part of security audit reporting i need to find out all logins who have the dbo role granted or have dbo aliases in all databases acorss our entire sql server estate (100s of servers)
Is it possible to pull this report centrally usinh linked servers created from a central server.
I am trying this option with cursors but unable to piece together as of now..
as the outer cursor will also include server name to loop through each server
DECLARE dbcursor CURSOR FOR
SELECTname
FROM['+@server+'].master.dbo.sysdatabases
open dbcursor
and the inner database cursor would include @server as well as @dbname as variables
Anyone with some inputs on approach to do this ?
Regards,
April 17, 2012 at 12:21 pm
You'd have to build it as dynamic SQL. It looks like that's the direction you're already going.
Have you looked into using a third-party solution? RedGate (site owner here) has a MultiScript product that might make it a lot easier and more reliable. Haven't looked into that, but it's probably worth checking.
If you go the dynamic SQL route and roll it yourself, then a query of sys.servers should get you what you need to loop through for that. Assuming your central server has linked servers set up for all of the other servers you need to audit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply