Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


System tables


System tables

Author
Message
jolan.mahinay
jolan.mahinay
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 94
hello to all,

i would like to ask if ever there are ways to copy the data from system tables which is sysusers, here's the scenario i have sqlserver 2000 enterprise and have tables and system tables, now i created another database to replicate those tables and data but i already done this but my problem is my client wants to export those 300 sql users to the replicated database which is found at sysusers system table but i was not able to export those data..anyone knows how to export those data?

thanks
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6845 Visits: 6121
If you want to transfer logins from ServerA to ServerB, there are a number of ways of doing it.

Can use the transfer logins task in DTS or SSIS depending which server you want to run the transfer on.

Lookup a procedure on Google called sp_help_revlogin, which creates a list of users with their passwords and SIDS which can be run on another server to recreate the SQL logins



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


jolan.mahinay
jolan.mahinay
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 94
do i need to add sql user one by one using the sp_help_revlogin? because when i run this script its generate a script like this EXEC master..sp_grantlogin 'BUILTIN\Administrators' then run successfully but i think this will input one by one..
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6845 Visits: 6121
If you run the procedure with no username it will generate the script for all logins, then you simply just need to copy and paste the output to a new query window and run it on the other server.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


jolan.mahinay
jolan.mahinay
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 94
thanks for the reply but still i got only one output still the administrator when i run this sp_help_revlogin
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6845 Visits: 6121
Appreciate that this may seem like a silly question, but you are running the procedure on the SQL 2000 server not the new server

Also you are running this version of sp_help_revlogin http://support.microsoft.com/kb/246133, not this one http://support.microsoft.com/kb/918992

There is a version for SQL 7 and 2000, and one for 2005 onwards.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


jolan.mahinay
jolan.mahinay
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 94
i tried to run this EXEC master..sp_help_revlogin from the source but still the output is only one..

/* sp_help_revlogin script
** Generated Sep 24 2012 1:11PM on JHOLS\SQL2000 */



/***** CREATE LOGINS *****/

-- Login: BUILTIN\Administrators
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'BUILTIN\Administrators')
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS



/***** SET DEFAULT DATABASES *****/

-- Login: BUILTIN\Administrators
ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=[master]



/***** SET SERVER ROLES *****/

-- Login: BUILTIN\Administrators
exec master.dbo.sp_addsrvrolemember @loginame='BUILTIN\Administrators', @rolename='sysadmin'
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6845 Visits: 6121
In that case there must only be that one login on the server.

Can you attach a screen shot of the security --> logins area in Enterprise Manager.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


georgesquared
georgesquared
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 47
No, if you're only getting one login, you're not running it as SA. You need the rights to read the logins from the source server and the rights to create logins on the destination side. Make sure you have SA rights. DBO is NOT sufficient.

I always review the output of sp_helprevlogin because it DOES list ALL logins, including already built-in logins such as SA. I like to comment out the ones I don't need so I don't get the spurious errors.

Once you run sp_helprevlogin, you shouldn't need to do anything else. Your user databases and their sysusers tables all work off SID. the nice thing about sp_helprevlogin is it reproduced the IDs exactly, including the SID & password, so all the user access should automagically reappear. It's a really cool tool.

Speaking of spurious errors, make sure you examine the output carefully. Some logins will invariably fail. The most likely cause is the SID is already taken, or the login name already exists, but with the wrong ID. You'll need to manually fix them, most likely by dropping the user login and maybe from each database and then recreating it manually. You can use the sp_helprevlogin code as a template to recreate the adjusted user ID. So, for example, you can recreate it with the same password.

One last caveat: sp_helprevlogin does not handle roles. So if you are moving DBA logins with SA rights, they will appear on the destination with no SA rights. You'll need to manually add that in yourself. You can massage sp_helprevlogin to handle roles, if you wish (I need to do that one day!)

Good luck,

G**2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search