By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Backing up Logins and Permissions for SQL Servers

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.