One of the DBAs on my team hadn’t seen this trick yesterday, so I thought it would be good to document it for persistence. I particularly like to use this technique when granting access to a specific object to user or role. This technique is also known as concatenating SQL.
Here is how you do in SQL Server–if you want to grant select on all tables in a database to some user.
select ‘grant select on ‘+name+’ to someuser;’ from sys.tablesYou will want to pay careful attention to your spaces, but the main concept is putting the text you want to generate in single quotes, and then using + signs around the name of the columns you are querying. Your result should look like this:
grant select on UNUSED_DBA_Maint_DBCC_CheckDB_Error_Results to someuser;For those of you who use Oracle, you can do the same thing there–just using pipes instead of plus signs.
select ‘grant select on ‘||table_name||’ to someuser;’ from dba_tables where owner=’SYS’;