on 06-23-2016 7:30 AM
Hello Experts,
In the process of Migration from oracle to mssql/2008 we are facing an error with r3trans -d with error "LOG BZA=>table SVERS does not exist on database"
I was referring other threads as well for resolution. I got through one to try
Even above script did not work for me. Below is the output of r3trans -d executed with SIDADM.
Also just to give a try I have searched for SVERS in SQL as
use SID
go
select * from SID.sid.SVERS
go
Output: Invalid object name SID.sid.SVERS
Tried the same like select * from SID.SVERS ,,, sid.SVERS,,, SVERS etc but the result was same.
Trans.log
4 ETW000 R3trans version 6.24 (release 721 - 12.04.16 - 20:16:02).
4 ETW000 unicode enabled version
4 ETW000 ===============================================
4 ETW000
4 ETW000 date&time : 23.06.2016 - 01:44:12
4 ETW000 control file: <no ctrlfile>
4 ETW000 R3trans was called as follows: R3trans -d
4 ETW000 trace at level 1 opened for a given file pointer
4 ETW000 [ dev trc,00000] Thu Jun 23 01:44:12 2016
4 ETW000 [ dev trc,00000] Loading DB library 'dbmssslib.dll' ...
4 ETW000 [ dev trc,00000] Library 'dbmssslib.dll' loaded
4 ETW000 [ dev trc,00000] Version of 'dbmssslib.dll' is "721.02", patchlevel (0.700)
4 ETW000 [ dev trc,00000] Thread ID:4008
4 ETW000 [ dev trc,00000] Thank You for using the SLODBC-interface
4 ETW000 [ dev trc,00000] Using dynamic link library 'D:\usr\sap\SID\SYS\exe\uc\NTAMD64\dbmssslib.dll'
4 ETW000 [ dev trc,00000] 7210 dbmssslib.dll patch info
4 ETW000 [ dev trc,00000] SAP patchlevel 0
4 ETW000 [ dev trc,00000] SAP patchno 700
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchlevel 0
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchno 700
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchcomment Support Package Stack Kernel 721 (EXT) Patch Level 700 (2316471)
4 ETW000 [ dev trc,00000] ODBC Driver chosen: SQL Server Native Client 10.0 native
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Thu Jun 23 01:44:13 2016
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Driver: sqlncli10.dll Driver release: 10.50.6000
4 ETW000 [ dev trc,00000] GetDbRelease: 10.50.6000.34
4 ETW000 [ dev trc,00000] GetDbRelease: Got DB release numbers (10,50,6000,34)
4 ETW000 [ dev trc,00000] ERROR: -1 in function CheckCodepageType (SQLExecDirect failed) [line 22233]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DBPROPERTIES'.
4 ETW000 [ dev trc,00000] comm. rd. spid 53
4 ETW000 [ dev trc,00000] unc. rd. spid 54
4 ETW000 [ dev trc,00000] Connection 0 opened (DBSL handle 0)
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'SVERS'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM SVERS
4 ETW000 /* R3::0 T:SVERS */
4 ETW000 [ dblink ,00000] ***LOG BZA=>table SVERS does not exist on database
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT COMPCNT, UNICODELG FROM "DDNTT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:DDNTT
4 ETW000 [ dev trc,00000] */
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM "DDNTT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:DDNTT */
4 ETW000 [dbntab.c ,00000] *** ERROR => NTAB: SELECT VERSION FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103
4 ETW000 [ dbntab ,00000] ***LOG BZY=>unexpected return code 103 calling NTAB
4 ETW000 [ dev trc,00000] NTAB: db_ntab(): init_ntab failed, returning 2
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT COMPCNT, UNICODELG FROM "XXXXT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:XXXXT
4 ETW000 [ dev trc,00000] */
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM "XXXXT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:XXXXT */
4 ETW000 [dbntab.c ,00000] *** ERROR => NTAB: SELECT VERSION FROM XXXXT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103
4 ETW000 [ dbntab ,00000] ***LOG BZY=>unexpected return code 103 calling NTAB
4 ETW000 [ dev trc,00000] NTAB: db_ntab(): init_ntab failed, returning 2
2EETW000 sap_dext called with msgnr "2":
2EETW000 ---- db call info ----
2EETW000 function: db_ntab
2EETW000 fcode: NT_RDTDESCR
2EETW000 tabname: TADIR
2EETW000 len (char): 5
2EETW000 key: TADIR
2EETW000 retcode: 2
4 ETW690 ROLLBACK "0" "0"
Can you guide me from this?
Best Regards,
Amarnath
Hello Amartnath,
I've faced the same error before. Here are the steps that I had executed for the solution:
Change the schema user in the profile files of SIDadm and oraSID user from source system to target system. Profile files: .*.sh files in Linux. I don't know your OS type, but the logic is similar.
Check your schema user from source system once again.
It should seem like like this:
hostname:TARGETSIDadm> grep -i sapSID .*sh
.dbenv_hostname.csh:if ( "sapSID" != "" ) then
.dbenv_hostname.csh:setenv dbs_db6_schema sapSID
.dbenv_hostname.csh:if ( "sapSID" != "" ) then
.dbenv_hostname.csh:setenv dbs_db6_user sapSID
.dbenv_hostname.sh:dbs_db6_schema=sapSID
.dbenv_hostname.sh:dbs_db6_user=sapSID
.dbenv.csh:if ( "sapSID" != "" ) then
.dbenv.csh:setenv dbs_db6_schema sapSID
.dbenv.csh:if ( "sapSID" != "" ) then
.dbenv.csh:setenv dbs_db6_user sapSID
.dbenv.sh:dbs_db6_schema=sapSID
.dbenv.sh:dbs_db6_user=sapSID
hostname:oraTARGETSID> grep -i sapSID .*sh
.dbenv_hostname.csh:if ( "sapSID" != "" ) then
.dbenv_hostname.csh:setenv dbs_db6_schema sapSID
.dbenv_hostname.csh:if ( "sapSID" != "" ) then
.dbenv_hostname.csh:setenv dbs_db6_user sapSID
.dbenv_hostname.sh:dbs_db6_schema=sapSID
.dbenv_hostname.sh:dbs_db6_user=sapSID
.dbenv.csh:if ( "sapSID" != "" ) then
.dbenv.csh:setenv dbs_db6_schema sapSID
.dbenv.csh:if ( "sapSID" != "" ) then
.dbenv.csh:setenv dbs_db6_user sapSID
.dbenv.sh:dbs_db6_schema=sapSID
.dbenv.sh:dbs_db6_user=sapSID
And then update the password via brtools. Example command:
brconnect -c -p initSID.sap -l E -f chpass -o SAPSR3 -password ******
Regards,
Serhat
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Amarnath,
Is the Migration done? Did you run the SCHEMA4SAP with correct user ID, if your SID is XXX, u should run the repair schema as Domain\XXXadm not as domain\xxxadm (means SID should be in UPPERCASE)
Regards,
kartik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Kartik,
Nope Migration has stopped in phase MSS Post load activities.
We are executing SWPM with user domain\username, stopped with error.
I ran the script with hostname\sidadm Can you correct me if this is not rigth? Also we do not have user domain\sidadm or domain\SIDadm.
Best Regards,
Amarnath
Hi Kartik,
I have tried as you mentioned. Used SAR files R3trans_625-10011176.SAR and lib_dbsl_700-10011175.sar. Rebooted the server and executed r3trans -d. But the result was same.
trans.log
4 ETW000 r3trans version 6.24 (release 721 - 15.03.16 - 20:16:01).
4 ETW000 ===============================================
4 ETW000
4 ETW000 date&time : 24.06.2016 - 00:48:54
4 ETW000 control file: <no ctrlfile>
4 ETW000 R3trans was called as follows: r3trans -d
4 ETW000 trace at level 1 opened for a given file pointer
4 ETW000 [ dev trc,00000] Fri Jun 24 00:48:55 2016
4 ETW000 [ dev trc,00000] Loading DB library 'dbmssslib.dll' ...
4 ETW000 [ dev trc,00000] Library 'dbmssslib.dll' loaded
4 ETW000 [ dev trc,00000] Version of 'dbmssslib.dll' is "721.02", patchlevel (0.700)
4 ETW000 [ dev trc,00000] Thread ID:3784
4 ETW000 [ dev trc,00000] Thank You for using the SLODBC-interface
4 ETW000 [ dev trc,00000] Using dynamic link library 'D:\usr\sap\SID\SYS\exe\uc\NTAMD64\dbmssslib.dll'
4 ETW000 [ dev trc,00000] 7210 dbmssslib.dll patch info
4 ETW000 [ dev trc,00000] SAP patchlevel 0
4 ETW000 [ dev trc,00000] SAP patchno 700
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchlevel 0
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchno 700
4 ETW000 [ dev trc,00000] Last MSSQL DBSL patchcomment Support Package Stack Kernel 721 (EXT) Patch Level 700 (2316471)
4 ETW000 [ dev trc,00000] ODBC Driver chosen: SQL Server Native Client 10.0 native
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID
4 ETW000 [ dev trc,00000] Driver: sqlncli10.dll Driver release: 10.50.6000
4 ETW000 [ dev trc,00000] GetDbRelease: 10.50.6000.34
4 ETW000 [ dev trc,00000] GetDbRelease: Got DB release numbers (10,50,6000,34)
4 ETW000 [ dev trc,00000] ERROR: -1 in function CheckCodepageType (SQLExecDirect failed) [line 22233]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DBPROPERTIES'.
4 ETW000 [ dev trc,00000] comm. rd. spid 52
4 ETW000 [ dev trc,00000] unc. rd. spid 53
4 ETW000 [ dev trc,00000] Connection 0 opened (DBSL handle 0)
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'SVERS'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM SVERS
4 ETW000 /* R3::0 T:SVERS */
4 ETW000 [ dblink ,00000] ***LOG BZA=>table SVERS does not exist on database
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT COMPCNT, UNICODELG FROM "DDNTT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:DDNTT
4 ETW000 [ dev trc,00000] */
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM "DDNTT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:DDNTT */
4 ETW000 [dbntab.c ,00000] *** ERROR => NTAB: SELECT VERSION FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103
4 ETW000 [ dbntab ,00000] ***LOG BZY=>unexpected return code 103 calling NTAB
4 ETW000 [ dev trc,00000] NTAB: db_ntab(): init_ntab failed, returning 2
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT COMPCNT, UNICODELG FROM "XXXXT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:XXXXT
4 ETW000 [ dev trc,00000] */
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.
4 ETW000 [ dev trc,00000] ERROR: -1 in function StartSelect (execute) [line 15183]
4 ETW000 [ dev trc,00000] (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
4 ETW000 [ dev trc,00000] Error 103 (dbcode 208) in StartSelect
4 ETW000 [ dev trc,00000] StartSelect: stmt of NULL_STMTID
4 ETW000 [ dev trc,00000] SELECT VERSION FROM "XXXXT" WHERE TABNAME = 'SVERS'
4 ETW000 /* R3::0 T:XXXXT */
4 ETW000 [dbntab.c ,00000] *** ERROR => NTAB: SELECT VERSION FROM XXXXT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103
4 ETW000 [ dbntab ,00000] ***LOG BZY=>unexpected return code 103 calling NTAB
4 ETW000 [ dev trc,00000] NTAB: db_ntab(): init_ntab failed, returning 2
2EETW000 sap_dext called with msgnr "2":
2EETW000 ---- db call info ----
2EETW000 function: db_ntab
2EETW000 fcode: NT_RDTDESCR
2EETW000 tabname: TADIR
2EETW000 len: 5
2EETW000 key: TADIR
2EETW000 retcode: 2
4 ETW690 ROLLBACK "0" "0"
Best Regards,
Amarnath
Hi Amarnath, How did you installed your target database SQL, using SQL4SAP or Manual installation.
is the collation is set to SQL_Latin1_General_CP850_BIN2, is the TCP/IP protocols enabled. Please check the SQL server installation guide for SAP and make sure database is correct or not.
Also, try to remove the sysadmin role for SIDadm user and see what you get.
Regards,
kartik
Hello Kartik,
Yes We have used the same collation you have mentioned. And we did manual installation.
Here is the log of schema repair and output : Query executed successfully.
/****************************************************************************
SCHEMA REPAIR SQL-script created by Schema4SAP.VBS
Version 1.09, Jan 2009: removed HASHED option
=================================================
SQL script for SQL Server 2005 (SQL 9.0) or newer
=================================================
SQL Instance: HOSTNAME\IVP
SID: IVP
database: IVP
ABAP schema: ivp
domain: HOSTNAME
creation day: 6/23/2016 1:33:40 AM
=================================================
To solve connection problems with this system:
1) check that the Windows-accounts exist:
HOSTNAME\ivpadm
HOSTNAME\SAPServiceIVP
2) set the user environment for HOSTNAME\ivpadm:
DBMS_TYPE = mss
MSSQL_DBNAME = IVP
MSSQL_SCHEMA = ivp
MSSQL_SERVER = <dbserver> or <dbserver>\<instance>
3) set the registry key:
Key: HKEY_LOCAL_MACHINE/Software/SAP/IVP
Type: REG_SZ
Value: AdmUser
String: HOSTNAME\ivpadm
4) set the SAP default profile parameters:
dbms/type = mss
dbs/mss/server = <dbserver> or <dbserver>\<instance>
dbs/mss/schema = ivp
dbs/mss/dbname = IVP
5) run this SQL script:
****************************************************************************/
-- check SQL instance name
declare @mySQLinstance sysname;
declare @error sysname;
set @mySQLinstance = cast(serverproperty('ServerName') as sysname);
set @error = 'This script is intended for SQL Server instance ''HOSTNAME\IVP'', ';
set @error = @error + 'but you are connected to ''' + @mySQLinstance + '''';
if upper(@mySQLinstance) != upper('HOSTNAME\IVP')
raiserror(@error ,11,1);
else begin
use [master];
-- generate random password
declare @cmd nvarchar(999);
declare @passwd sysname;
set @passwd = 'RAND-pwd.' + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);
set @passwd = @passwd + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);
-- recreate Windows Login HOSTNAME\ivpadm (Windows account must already exist)
if exists (select * from sys.syslogins where name = 'HOSTNAME\ivpadm')
drop login [HOSTNAME\ivpadm];
create login [HOSTNAME\ivpadm] from windows with default_database = [IVP];
exec sp_addsrvrolemember 'HOSTNAME\ivpadm', 'sysadmin';
-- recreate Windows Login HOSTNAME\SAPServiceIVP (Windows account must already exist)
if exists (select * from sys.syslogins where name = 'HOSTNAME\SAPServiceIVP')
drop login [HOSTNAME\SAPServiceIVP];
create login [HOSTNAME\SAPServiceIVP] from windows with default_database = [IVP];
exec sp_addsrvrolemember 'HOSTNAME\SAPServiceIVP', 'sysadmin';
-- if not exists: create SQL Login ivp (random password, disabled)
set @cmd = 'create login [ivp] with password=''' + @passwd + ''', check_policy = off;';
if not exists (select * from sys.syslogins where name = 'ivp')
execute(@cmd);
alter login [ivp] with default_database = [IVP], check_expiration = off, check_policy = off;
alter login [ivp] disable;
exec sp_addsrvrolemember 'ivp', 'serveradmin';
exec sp_addsrvrolemember 'ivp', 'dbcreator';
exec sp_addsrvrolemember 'ivp', 'bulkadmin';
exec sp_dropsrvrolemember 'ivp', 'sysadmin';
use [IVP];
-- change database owner to sa
exec sp_changedbowner [sa];
-- create user and schema ivp
if not exists (select * from sys.database_principals where name='ivp' and type = 'S')
create user [ivp] for login [ivp];
alter user [ivp] with default_schema = [ivp];
exec sp_addrolemember 'db_owner', 'ivp';
exec sp_droprolemember 'db_denydatareader', 'ivp';
exec sp_droprolemember 'db_denydatawriter', 'ivp';
exec sp_change_users_login 'Update_One', 'ivp', 'ivp';
if not exists (select * from sys.schemas where name = 'ivp')
exec('create schema [ivp]');
alter authorization on schema::[ivp] to [ivp];
use [msdb];
-- create user and schema ivp
if not exists (select * from sys.database_principals where name='ivp' and type = 'S')
create user [ivp] for login [ivp];
alter user [ivp] with default_schema = [ivp];
exec sp_addrolemember 'db_owner', 'ivp';
exec sp_droprolemember 'db_denydatareader', 'ivp';
exec sp_droprolemember 'db_denydatawriter', 'ivp';
exec sp_change_users_login 'Update_One', 'ivp', 'ivp';
if not exists (select * from sys.schemas where name = 'ivp')
exec('create schema [ivp]');
alter authorization on schema::[ivp] to [ivp];
use [master];
-- create user and schema ivp
if not exists (select * from sys.database_principals where name='ivp' and type = 'S')
create user [ivp] for login [ivp];
alter user [ivp] with default_schema = [ivp];
exec sp_addrolemember 'db_owner', 'ivp';
exec sp_droprolemember 'db_denydatareader', 'ivp';
exec sp_droprolemember 'db_denydatawriter', 'ivp';
exec sp_change_users_login 'Update_One', 'ivp', 'ivp';
if not exists (select * from sys.schemas where name = 'ivp')
exec('create schema [ivp]');
alter authorization on schema::[ivp] to [ivp];
grant execute on xp_cmdshell to [ivp];
grant control server to [ivp];
-- end of 'check SQL instance name'
end;
Best Regards,
Amarnath
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.