Copying user permissions from a database.

Lawrence M. Seldin, CMC, CPC

unread,

Jun 19, 1998, 3:00:00 AM6/19/98

to

I have 2 Servers, Production and Development.

I do a dump from Production database to Development database nightly. When I do this, I lose user
permissions and login Id's that were on the existing Development database, however, data migrates
fine. My customer wants to keep the existing permissions that he has granted on the Development
database as userid's are different on the Development database. One approach is to query system
catalog and determine what permission do users currently have in the Development database and
regrant them permissions after the load database takes place from the Production Database.

If I want to query system tables what tables and fields do I need? As an alternative, can I also
dump master besides the user database and will this give me all user permissions when I load to the
new database on another server?

I can easily do this manually with a tool such as DBArtison, but I want to place a job in Cron to do
this for me.

I am running HP Unix 10.x and Sybase 11.

If I am going down wrong path, please give me suggestions. I want to run this job on a nightly
basis.

TIA,

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP

INTERNET: sel...@i-2000.com
COMPUSERVE: 75567...@compuserve.com
http://ourworld.compuserve.com/homepages/seldin

Jim Prather

unread,

Jun 20, 1998, 3:00:00 AM6/20/98

to

Use Artisan to create the grant script for you and include this script after
the load operation in your batch....

Jim Prather
jl...@jorsm.com

Lawrence M. Seldin, CMC, CPC wrote in message
<358cb0b6...@news.i-2000.com>...

Lawrence M. Seldin, CMC, CPC

unread,

Jun 22, 1998, 3:00:00 AM6/22/98

to

>Use Artisan to create the grant script for you and include this script after
>the load operation in your batch....

Jim,

My problem is I these permissions can change frequently. So I do not want to manually do this in
DBArtisan.

I need a "batch job" that will record table/user grants and then regrant them nightly.

Any suggestions?

willia...@chase.com

unread,

Jun 23, 1998, 3:00:00 AM6/23/98

to

In article <359363e1...@news.i-2000.com>,

Lawrence:

Here's a little query I reverse-engineered from the sp_helprotect system
procedure. It generates grant and revoke statements for most basic
permissions setups. I haven't had it miss anything yet but of course that is
no guarantee. Make sure you are in the right db before running, and send the
output to a file.

select t.name+' '+a.name+' on '+o.name+' to '+ user_name(p.uid)+'
go','
'
from master..spt_values t, master..spt_values a, sysprotects p,
sysobjects o
where o.type != "S"
and p.id = o.id
and t.type = 'T' and a.type = 'T'
and (t.number = (p.protecttype+204)
or (t.number = (p.protecttype + 205)
and p.protecttype = 0))
and a.number = p.action
and t.name != null and a.name != null
go

Hope this helps too!

** NOTE ** This query doesn't work on pre-11 servers. **

- Bill Blake
willia...@chase.com
wbl...@nac.net

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading

Lawrence M. Seldin, CMC, CPC

unread,

Jun 23, 1998, 3:00:00 AM6/23/98

to

Bill Blake,

I tested this query in isql and it works great. I will try it tomorrow on my test database.

Thank you very much for this query. It looks great.

PS. What stored procedure gives me login informations so I can create sp_addlogin scripts..

I want to take your script and also do sp_addlogin and sp_adduser for my program. I know I can use
sp_helpuser for users in a database. I also need the sp_ command for login info.