|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/1/2009 9:58:26 AM
Posts: 7,
Visits: 36
|
|
Hello,
Is there a way to find and extract the permissions of objects using bteq?
I want to create an output file that scripts out the permissions for a specific object (table, stproc, macro etc...)
ex.
grant select on db.tableA to userA;
Thank you for your help.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 7:09:10 PM
Posts: 1,
Visits: 16
|
|
|
You should be able to query dbc.allrolerights and find list of accesses granted and who granted it.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/1/2009 9:58:26 AM
Posts: 7,
Visits: 36
|
|
Thanks for the information. So what your saying is the easiest way to create a script of object permissions would be to create a dynamic query such as:
Substituing the dbc.allrolerights.accessright column with some kind of convert clause
select 'grant', (INSERT CONVERSION FUNCTION for accessright column here), on ', databasename,'.',tablename,'to ',RoleName,';' from dbc.allrolerights where tablename = 'whatever_table'
to form something like this...
grant select on database.tablename to rolename;
It seems like overkill. Is there something else like DDL extract functionality or something simpler like: SHOW TABLE tablex (which includes permissions on that table?)
Cheers
rrenn001
|
|
|
|