|
|
Posted by David Portas on 09/25/04 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!
I answered when you posted the same question a month ago:
http://groups.google.co.uk/group/comp.databases.ms-sqlserver/browse_frm/thread/eea72f7dd6c5e92a/2884776f21c04331?hl=en#2884776f21c04331
I'm not sure why you would want a stored proc to do this since it only
takes three keystrokes to execute the output of a SELECT statement
(cut/paste/execute). If you need to automate it then use OSQL or SQLCMD
both of which are documented in Books Online. But since you haven't got
past the stage of "Why isn't SQL Server identical to Oracle?" maybe you
haven't got round to reading anything yet...
Hope this helps.
--
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]
|