|
|
Posted by David Portas on 09/25/96 11:59
a_dba_used_to_oracle wrote:
> I give up - will someone please tell me why in _Oracle_ I can do
> something like:
>
>
> select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
> SYSADM.'||TABLE_NAME||';'
> from dba_tables where owner='SYSADM';
>
> and get:
>
> CREATE PUBLIC SYNONYM CHAINED_ROWS FOR SYSADM.CHAINED_ROWS;
> CREATE PUBLIC SYNONYM CH_CAP FOR SYSADM.CH_CAP;
> CREATE PUBLIC SYNONYM PSACTIVITYDEFN FOR SYSADM.PSACTIVITYDEFN;
> CREATE PUBLIC SYNONYM PSACTIVITYDEL FOR SYSADM.PSACTIVITYDEL;
>
> etc, etc, etc, for ALL tables owned by SYSADM
>
> However, in SQL Server, I simply CANNOT write:
>
> select 'grant select, insert, update, delete on '+table_name+ ' to
> psselect'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
>
> and have it write out to a SQL script file that can be executed!!!!
>
> I have tried something like the following:
>
> create procedure count_ps_tables @pscount int OUTPUT
> AS
> select table_name from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> set @pscount=@@ROWCOUNT
> return(0)
> exec count_ps_tables @t_count OUTPUT
> begin
> while @t_count !=0
> declare @grant_stmt varchar(133)
> select @grant_stmt ='grant select on '+table_name+ ' to PSSELECT'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> order by table_name
> print @grant_stmt
> exec (@grant_stmt)
> set @t_count = @t_count - 1
> end
>
> but it _does not work_
>
> If anyone can tell me - I would be ever so grateful!
Also, you can grant permissions directly on a schema. See Books Online
for details. Example:
GRANT SELECT ON SCHEMA::schema_name TO PSSELECT;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|