user_change.sql

5 pts.
Tags:
SAP ECC 6.0
SQL Server
in order to change the table names to the target system sid 

after performing a restore of database from another system will the following code work

-- 

-- Before running this script, replace the following[Br type="_moz" />

--    @NAME@       - your new <SID> in lowercase (prd for example).[Br type="_moz" />

--    @PASSWORD@   - a password for your @NAME@ sid login which will be created[Br type="_moz" />

--    @DB_SID@     - your new <SID> in uppercase (same as the db name)

--    @HOSTNAME@   - your computer host name

--    @PASSWORD1@  - password of local user SAPMssXPUser

--    @SIDADM@     - DOMAINsidadm, the r3 system administrator OS login. PRODprdadm for example

--                   NOTE: The case is important.  Should be all lowercase.

--    @SAPSERVICE@ - DOMAINSAPServiceSID, the r3 service OS login. PRODSAPServicePRD for example 

--                   NOTE: Case is also important here, and it should be exactly as shown above.

--    [Br type="_moz" />

 use master[Br type="_moz" />

EXEC sp_addlogin '@NAME@', '@PASSWORD@', @DB_SID@[Br type="_moz" />

go

use @DB_SID@

EXEC sp_change_users_login 'Update_One','@NAME@','@NAME@'

EXEC sp_grantdbaccess '@NAME@'

EXEC sp_addrolemember 'db_owner', '@NAME@'

go

use master

EXEC sp_grantdbaccess '@NAME@'

EXEC sp_addrolemember 'db_owner', '@NAME@'

go

use msdb

EXEC sp_grantdbaccess '@NAME@'

EXEC sp_addrolemember 'db_owner', '@NAME@'

go

use model

EXEC sp_grantdbaccess '@NAME@'

EXEC sp_addrolemember 'db_owner', '@NAME@'

go

use tempdb

EXEC sp_grantdbaccess '@NAME@'

EXEC sp_addrolemember 'db_owner', '@NAME@'

go

use master

grant all on xp_cmdshell to @NAME@

go

EXEC sp_addsrvrolemember '@NAME@', 'serveradmin'

EXEC sp_addsrvrolemember '@NAME@', 'dbcreator'

EXEC sp_addsrvrolemember '@NAME@', 'bulkadmin'

go

EXEC xp_sqlagent_proxy_account 'SET', '@HOSTNAME@', 'SAPMssXPUser', N'@PASSWORD1@'

go

EXEC sp_grantlogin   '@SIDADM@'

EXEC sp_grantlogin   '@SAPSERVICE@'

EXEC sp_defaultdb    '@SIDADM@','@DB_SID@'

EXEC sp_defaultdb    '@SAPSERVICE@','@DB_SID@'

go 

EXEC sp_addsrvrolemember '@SIDADM@',     'sysadmin'

EXEC sp_addsrvrolemember '@SAPSERVICE@', 'sysadmin'

go



Software/Hardware used:
sql server, ecc6

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following