Backing up Logins and Permissions for SQL Servers

[happyjenny52]

Hi,

I work as an IT Administrator and part of my job role is to use
Enterprise Manager and Query Analyzer to backup the logins and
permissions for each database on our SQL Servers. This information is
used as a backup in the event of a server failing so we then have
something to fall back on if we need to add the same logins/permissions
again.

This takes ages to do, as I have to manually enter all the information
into a spreadsheet.

Steps:
- In Enterprise Manager I connect to a server then 'Security' tab, then
'Logins'. I then enter all items into a spreadsheet.
- In Query Analyzer I connect to a server then do 'sp_helpuser' to list
all the permissions for each database. I then enter all information
into a spreadsheet.
I was wondering if there is such a program available on the market that
would do this for me? I know that MS Baseline Security can show flat
file share permissions to a certain degree and was wondering if there
was a similar program out there to help me do my job easier, or if
there is a simple way of doing this in the future...

Any feedback would be greatly appreciated.

Regards,
Jenny

[Tony Rogerson]

Run SQL profiler and capture the SQL enterprise manager is throwing at the
server and then you can customise this and get DTS to output your
spreadsheet for you.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ha**********@hotmail.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...

Hi,

I work as an IT Administrator and part of my job role is to use
Enterprise Manager and Query Analyzer to backup the logins and
permissions for each database on our SQL Servers. This information is
used as a backup in the event of a server failing so we then have
something to fall back on if we need to add the same logins/permissions
again.

This takes ages to do, as I have to manually enter all the information
into a spreadsheet.

Steps:
- In Enterprise Manager I connect to a server then 'Security' tab, then
'Logins'. I then enter all items into a spreadsheet.
- In Query Analyzer I connect to a server then do 'sp_helpuser' to list
all the permissions for each database. I then enter all information
into a spreadsheet.
I was wondering if there is such a program available on the market that
would do this for me? I know that MS Baseline Security can show flat
file share permissions to a certain degree and was wondering if there
was a similar program out there to help me do my job easier, or if
there is a simple way of doing this in the future...

Any feedback would be greatly appreciated.

Regards,
Jenny

[m.bohse]

Jenny,

check out this DumpSql Permissions. It's not perfect (a few bugs) but
it's free.
http://www.sqlservercentral.com/colu...ermissions.asp

Markus