April 24, 2015 at 9:31 am
I have a situation to write this code in SQL to used in SSIS.
if table1.username in (table2.username)
(and table1.status <> not active
then revoke table2.username permissions on server and update table.date column = today-1
)
if else
If table1.status = active then
create login for table1.user name in sql server and grant only one database role.
Else
if table1.status = 512 and table1.username not in table2.username
then create login for table1.username and grant database role
and also
insert table1.name in table 2
Please give some ideas, how can I write the above logic in T-sql.
Many Thanks in Advance
April 24, 2015 at 10:51 am
Are table1.username and table2.username columns? I'm assuming you mean that you want to check if any of the values in table1.username exist in table2.username. That's an easy check:
if exists (select t1.username
from table1 t1
inner join table2 t2
on t1.username = t2.username
)
That gives you those rows that are matching in the tables.
Once you have that, then the update is simple. Use joins to write an update. The revoke is hard, as you can't batch the revoke. I believe you'd need to use a cursor or other construct to run the REVOKE statement for each item.
For the ELSE you'd do something similar for the CREATE LOGIN and GRANT statements.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 24, 2015 at 11:34 pm
Yes, they are columns of two tables, the requirement is all they what this batch of code run in ssis package or job.
They are expecting to run with package with a single click.which consists all these code to run at one go.
April 25, 2015 at 9:17 am
If it's in SSIS, I know you can loop a bit in there. Not sure exactly how, but I suspect you might be able to loop through a result set and execute some code in the loop that's based on variables (user names) that come from the result set.
I'll have to point some SSIS experts at this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2015 at 9:29 am
You can populate a result set using a query, and then use a For Each Loop in SSIS to loop through the resulting output. Though your setup would likelier be more complicated than this example, you could probably use a design pattern similar to one I blogged about here[/url].
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 27, 2015 at 2:53 am
Can you give suggestions how to write this script in SQL for above code.
Many Thanks in advance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply