Post reply

Script to scan server logins for database permissions

  • July 21, 2006 at 12:21 pm

    #191574

    To all scripting gurus!!

    I have to maintain interior security for over 1500 SQL Server (2000), and need a scanning script that will run through the list of all logins in the security folder in Enterprise Manager for each server and pull out and display (not the server roles but) the default database and all database permissions for each login. Any suggestions?

     

    Thanks for your help!!

     

    Jim Ruddy

  • July 24, 2006 at 6:03 am

    #651004

    you can write a script to do this, the biggest problem is getting it to traverse the databases on the server, I usually make use of the sp_msforeachdb undoc ( sort of ) procs.

    I wrote procs which I placed in master to achieve this, but mine actually scripted out the users. roles and permissions as create scripts. ( I needed this functionality ) The RK contains an electronic system table map which may prove useful to you.

    I guess if you placed your routines centrally you could poll them remotely in turn back to a central location or you could generate html pages on each server to a central location.

    Hope this helps.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 24, 2006 at 6:04 am

    #651006

    actually sp_helplogins may do what you require - it didn't fit what I needed.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply