469,646 Members | 1,100 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Backing up Logins and Permissions for SQL Servers

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

May 31 '06 #1
2 1843
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

May 31 '06 #2
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

May 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ike | last post: by
1 post views Thread by Ryan | last post: by
1 post views Thread by David McGeorge | last post: by
10 posts views Thread by Lyle Fairfield | last post: by
3 posts views Thread by Simon Watkins | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.