0

I have 470 new database accounts in SYBASE ASE

I certainly don't want to do it manually since it is definitely time consuming.

Here by I have old script which I try to add logins

Can someone help to me write this script in another way?

Data present in this file will be like as below /tmp/addthese.txt:

i111111
i222222
i333333
i444444
i555555
i666666
i777777
i888888
i999999

SCRIPT : source /cis1/sybase/SYBASE.sh

set passs=`cat /cis1/prod/dba/input/. ----`

isql -Usa -SPDSQTS1 <<EO_SQL
$passs

declare a varchar(10)
declare n int
declare count int
declare Iuser varchar(10)
declare date varchar(20)
declare id int

count=1

while n<427

a=sed -n 'count{p;q;}' "/tmp/addthese.txt"

select Iuser=name from syslogins where name='a'

if [Iuser!=a]
sp_addlogin a,Sybase123
go

use qts_db
go
sp_adduser a,a,eng
go
use engcomdb
go
sp_adduser a,a,eng
go
use sybasedb
go
sp_adduser a,a,eng
go
use engdb
go
sp_adduser a,a,eng
go
use msgdb
go
sp_adduser a,a,eng
go


use master
go
sp_modifylogin 'a',"authenticate with","ldap"
go

select id=suid from syslogins where name='a'
select date=crdate from syslogins where name='a'

use qts_db
go

insert into dbo.profiles(login_name,userid,security_level,delta_ts,delta_sy_user) values ('a',id,'ADMIN','date',id)
go

insert into dbo.users(userid,site_id,delta_sy_user,delta_ts,email,displayname,lastname,firstname) 
values(id,'SYBASE',10407,'date','a',',  : id','', '')
go


end

count=count+1

end
  • 1) there's no way that 'old' script could have ever added logins to Sybase ASE as there's a lot of invalid syntax going on; 2) if you're working with Sybase ASE then I'd suggest you remove the sybase-sql-anywhere and sybase-central tags, and possibly consider adding sybase-ase; 3) I'd also suggest you provide a sample of the data from the /tmp/addthese.txt file – markp Jun 21 '17 at 14:08
  • Hi mark, It will be like i111111 i222222 i333333 i444444 i555555 i666666 i777777 i888888 i999999 – Arun Jun 21 '17 at 14:15
  • please add that sample data to your original post; in particular we need to know if those values (i111111, i222222) are on separate lines or concatenated into one very long line; also, I'm not sure what the 'sed -n ...' is supposed to be doing in this case so an example of what you're attempting to do would be good, too; thanks – markp Jun 21 '17 at 14:22
  • In that file ID's will be available one by one below – Arun Jun 21 '17 at 14:28
1

There are many, many ways you could handle adding a list of logins/users to Sybase ASE, all of which include a mix of (Unix/Linux) shell scripting and T-SQL coding.

One simple example:

set passs=`cat /cis1/prod/dba/input/. ----`

cat addthese.txt | while read newlogin
do
isql -Usa -SPDSQTS1 <<EO_SQL > /tmp/error.out 2>&1
$passs
use master
go
if suser_id("${newlogin}") is NULL
    exec sp_addlogin ${newlogin},Sybase123
go
----------------------
-- repeat following block for each db of interest (eg, qts_db, engcomdb)
use <db>
go
if user_id("${newlogin}") is NULL
    exec sp_adduser ${newlogin}, ${newlogin}, eng
go
---------------------
-- rest of your coding
go
EO_SQL

... check for errors in /tmp/error.out ...

done

You could place all of the above into a shell script, make sure addthese.txt is correct/up-to-date, and then run the shell script.


Variations on the above could include:

  • putting the T-SQL code in a stored proc and then calling the stored proc for each new login

  • placing the above T-SQL code in a template.sql file with NEWLOGIN used as place holder, a shell script would then run sed 's/NEWLOGIN/${newlogin}/g' template.sql > runme.sql followed by isql -i runme.sql > error.out 2>&1

There are many variations based on which shell you're using and your coding style/preferences.

0

source /cis1/sybase/SYBASE.sh set passs=cat /cis1/prod/dba/input/. ----

cat addthese.txt | while read newlogin do isql -Usa -SPDSQTS1 < /tmp/error.out 2>&1 $passs use master go if suser_id("${newlogin}") is NULL exec sp_addlogin ${newlogin},Sybase123

go

-- repeat following block for each db of interest (eg, qts_db, engcomdb) use qts_db go if user_id("${newlogin}") is NULL exec sp_adduser ${newlogin}, ${newlogin}, eng go

use engcomdb go if user_id("${newlogin}") is NULL exec sp_adduser ${newlogin}, ${newlogin}, eng go

use sybasedb go if user_id("${newlogin}") is NULL exec sp_adduser ${newlogin}, ${newlogin}, eng go

use engdb go if user_id("${newlogin}") is NULL exec sp_adduser ${newlogin}, ${newlogin}, eng go

use msgdb go if user_id("${newlogin}") is NULL exec sp_adduser ${newlogin}, ${newlogin}, eng go

use master go sp_modifylogin ${newlogin},"authenticate with","ldap" go

select id=suid from syslogins where name=${newlogin} select date=crdate from syslogins where name=${newlogin}

use qts_db go

insert into dbo.profiles(login_name,userid,security_level,delta_ts,delta_sy_user) values (${newlogin},id,'ADMIN','date',id) go

insert into dbo.users(userid,site_id,delta_sy_user,delta_ts,email,displayname,lastname,firstname) values(id,'SYBASE',10407,'date',${newlogin},',  : id','', '') go

EO_SQL

... check for errors in /tmp/error.out ...

done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.