System tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » System tables

System tables

Rate Topic

Display Mode

Topic Options

Author

Message

jolan.mahinay

Posted Friday, September 21, 2012 3:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 05, 2012 2:24 AM
Points: 43, Visits: 92

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

Post #1362499

anthony.green

Posted Friday, September 21, 2012 3:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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
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

Post #1362501

jolan.mahinay

Posted Friday, September 21, 2012 3:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 05, 2012 2:24 AM
Points: 43, Visits: 92

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..

Post #1362513

anthony.green

Posted Friday, September 21, 2012 3:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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.

Post #1362515

jolan.mahinay

Posted Friday, September 21, 2012 4:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 05, 2012 2:24 AM
Points: 43, Visits: 92

thanks for the reply but still i got only one output still the administrator when i run this sp_help_revlogin

Post #1362541

anthony.green

Posted Friday, September 21, 2012 4:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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.

Post #1362544

jolan.mahinay

Posted Sunday, September 23, 2012 11:17 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 05, 2012 2:24 AM
Points: 43, Visits: 92

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'

Post #1363335

anthony.green

Posted Monday, September 24, 2012 1:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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.

Post #1363354

georgesquared

Posted Tuesday, September 25, 2012 9:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:32 AM
Points: 4, Visits: 44

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

Post #1364113

« Prev Topic | Next Topic »

Permissions