0

Im looking for a sql command that select all objects and grants permissions, something like below, but to select and grant at the same time, and not to spin out another sql to be executed

select 'grant alter any table on ' + name + ' to USER1' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P'

UPDATE

Here is the syntax I have to create a proc - I don't have experience on it HELP I have to have a proc that selects all tables and grants permissions to users

create or replace procedure 
grant_permissions @table varchar (30) = "sys%" 
as 
select 'grant alter any table on ' + name + ' to user1' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P'
go
select 'grant alter any table on ' + name + ' to user2' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P'
go
markp-fuso
  • 28,790
  • 4
  • 16
  • 36
Helena
  • 1
  • 1
  • HI thank you, what Im trying is a select that will grant permissions to all objects in a db select 'grant alter any table on ' + name + ' to USER ' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P' – Helena Mar 04 '21 at 21:27
  • a `select` cannot grant permissions; you can use the `select` to **generate** the `grant` commands, but then you'll need some other method to **execute** those `grant` commands; the whole thing could be done in T-SQL via a cursor/looping construct and `execute()` calls, or you could have your calling process (eg, shell script) parse out the `grant` commands (from the result set generated by the `select`) and then issue the `grant` commands over another `isql` session – markp-fuso Mar 04 '21 at 21:30
  • I have it working in a shell script and cant use the shell in this situation, however, trying to create a PROC to do it, Im having issues create or replace procedure test @table varchar (30) = "sys%" as select 'grant alter any table on ' + name + ' to USER + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P' go select 'grant alter any table on ' + name + ' to USER' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P' go – Helena Mar 04 '21 at 21:34
  • what do you mean by `having issues`? please update the question with the details of the command(s) you're issuing and a detailed explanation of what you mean by `having issues` – markp-fuso Mar 05 '21 at 14:28
  • Hi - I'm trying to create a storedProc and having issues , this is the syntax I have so far, i never did one before create or replace procedure grant_permissions @table varchar (30) = "sys%" as select 'grant alter any table on ' + name + ' to user1' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P' go select 'grant alter any table on ' + name + ' to user2' + CHAR(10) + 'go' from sysobjects where type = 'U' or type = 'V' or type = 'P' go – Helena Mar 05 '21 at 14:52
  • that still doesn't tell us what the issue is you're having ... syntax error so proc is not created? proc is created but errors out when running? runs but does not do what you want? code is hard to read in comments so I'd suggest you update the question with the full contents of your code; also, what you've provided in your comment looks like 2 different commands ... one == 'create proc' ... two == 'select' (not part of proc) ... so not sure what you're trying to accomplish ... – markp-fuso Mar 05 '21 at 15:56
  • thanks - i have updated the question – Helena Mar 05 '21 at 18:53
  • the proc, as currently coded, merely runs a `select` statement; the follow-on separate `select` statement ... has nothing to do with the proc creation and just ... runs a `select` (so not sure why it's posted here ...?); if the intent is to have the proc take the output from the `select` statement and actually run the generated `grant` commands then you'll need to (in the proc) define a cursor that runs the `select` statement, add a `while` loop to step through the results of the cursor/`select` and `execute()` on each pass through the loop ... – markp-fuso Mar 05 '21 at 19:32
  • for starters take a look at this [example of a stored proc with a cursor](http://infocenter-archive.sybase.com/help/topic/com.sybase.dc33621_33620_33619_1250/html/ptallbk/X61512.htm); you can find more info about the `execute()` construct in the ASE Reference/Commands manual ... I suggest you start by assigning a 'grant' command to a @variable and then `execute(@variable)` to see how it works ... from there it shouldn't be too hard to feed add this to a proc/cursor solution – markp-fuso Mar 05 '21 at 19:33

0 Answers0