Search code examples
sybasesap-ase

How to add user at once in all databases?(Sybase-ASE)

I want to add a user to all databases with the following command sp_adduser at once to all databases. What query can give access to all databases at once? Is this generally possible to do in the Sybase?

Solution

  • This might work from the shell script:

    user=<your new sybase user>

isql -U ${UID} -P ${PWD} -S ${SERVER} <<! | tee $0.out
set nocount on
go
declare cur cursor for select name from master..sysdatabases order by 1
go
declare @l_name varchar(30), @l_sql varchar(777)
begin
  open cur
  fetch cur into @l_name
  while @@sqlstatus = 0
  begin
    print @l_name 
    fetch cur into @l_name
  end
  close cur
  deallocate cur
end  
go
!

for dbname in `cat ${0}.out`; do
  echo "
use ${dbname}
go
sp_adduser ${user}
go
" >>${0}.sql
done

isql -U ${UID} -P ${PWD} -S ${SERVER} -i ${0}.sql