February 12, 2016 at 3:22 pm
I have a script which basically scripts out all the users along with permissions needed, my script will spit a script that should be executed to grant permissions. My problem is i have quite a few logins that i need to find/replace in the script, is there a good way to find/replace text? This might more generic question, i am thinking power shell could do it but never tried.
February 13, 2016 at 2:15 am
curious_sqldba (2/12/2016)
I have a script which basically scripts out all the users along with permissions needed, my script will spit a script that should be executed to grant permissions. My problem is i have quite a few logins that i need to find/replace in the script, is there a good way to find/replace text? This might more generic question, i am thinking power shell could do it but never tried.
Copy the script in an SSMS query window and hit Ctrl-H?
Or save it, load it in your favorite text editor?
Depending on what you exactly need, it might also be possible to change the query that produces the script.
February 13, 2016 at 12:12 pm
Hugo Kornelis (2/13/2016)
curious_sqldba (2/12/2016)
I have a script which basically scripts out all the users along with permissions needed, my script will spit a script that should be executed to grant permissions. My problem is i have quite a few logins that i need to find/replace in the script, is there a good way to find/replace text? This might more generic question, i am thinking power shell could do it but never tried.Copy the script in an SSMS query window and hit Ctrl-H?
Or save it, load it in your favorite text editor?
Depending on what you exactly need, it might also be possible to change the query that produces the script.
I have a table something like this:
CurrentLoginName UpdatedLoginName
domain1\John domain2\John
I want an automated way which will basically loop through a table and replace whereever domain1\john is with domain2\john, i have 100+ logins to be replaced. Modifying the existing code to do replace is tedious, so basically script should take a .sql file,loop through the table and replace the values and create a new script with updated values.
February 13, 2016 at 1:06 pm
UPDATE SomeTable
SET SomeColumn = 'domain2\John'
WHERE SomeColumn = 'domain1\John';
If I am still misunderstanding you, then please post your question in a more precise form. No "table something like this" but an actual CREATE TABLE statement. No "data something like this" but a few INSERT statements with sample data, carefully chosen to illustrate what needs to be done. And a list of the exact expected results based on that sample input, plus an explanation to clarify.
February 13, 2016 at 5:34 pm
curious_sqldba (2/13/2016)
Hugo Kornelis (2/13/2016)
curious_sqldba (2/12/2016)
I have a script which basically scripts out all the users along with permissions needed, my script will spit a script that should be executed to grant permissions. My problem is i have quite a few logins that i need to find/replace in the script, is there a good way to find/replace text? This might more generic question, i am thinking power shell could do it but never tried.Copy the script in an SSMS query window and hit Ctrl-H?
Or save it, load it in your favorite text editor?
Depending on what you exactly need, it might also be possible to change the query that produces the script.
I have a table something like this:
CurrentLoginName UpdatedLoginName
domain1\John domain2\John
I want an automated way which will basically loop through a table and replace whereever domain1\john is with domain2\john, i have 100+ logins to be replaced. Modifying the existing code to do replace is tedious, so basically script should take a .sql file,loop through the table and replace the values and create a new script with updated values.
Are you saying that you don't really care if it's John or not and that you just want to replace all of a particular domain with another? And please see the first link in my signature line below to help us help you better.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2016 at 8:09 pm
If it is in a table like you say cant you just use replace ?
replace (col, 'domain1', 'domain2')
Or am i totally missing the point here ?
February 14, 2016 at 8:25 pm
matak (2/14/2016)
If it is in a table like you say cant you just use replace ?replace (col, 'domain1', 'domain2')
Or am i totally missing the point here ?
Add a slosh to that just to tighten things up a bit and I'd say spot on!
REPLACE(col,'domain1\','domain2\')
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2016 at 1:17 pm
matak (2/14/2016)
If it is in a table like you say cant you just use replace ?replace (col, 'domain1', 'domain2')
Or am i totally missing the point here ?
The point you are missing is that we still have no idea what the original poster really wants/needs. The question is too vague, and this is purely based on assumptions made by various people as to what the OP requires.
February 16, 2016 at 9:03 am
Apologies for confusion, i don't know how to draw a table here, so putting in text format. Let me put the steps that i am using:
i) I use a script which will crawl through all the databases and generate a script ( like the one below) required permissions.
ii) I take the generated script and manually find replace ( there are about 40 logins) in the script.
iii) Take the updated script with newly replaced logins and execute on target server.
Instead of all this, i wanted to have a table something like this and a process should look at the table, find and replace the values and save an updated script. Hopefully this is clear
CurrentName ToBeChanged
domain1\John domain2\IQ_John
domain1\Mary domain2\CQ_Mary
domain1\Sam domain2\EZ_Sam
USE [MyDb];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'domain1\John')
CREATE USER [domain1\John] WITH DEFAULT_SCHEMA = [dbo];
GO
USE [MyDb];
GO
EXECUTE sp_AddRoleMember 'db_owner', 'domain1\Mary';
GO
February 16, 2016 at 12:56 pm
So the assumption made previously that you need to change the domain name for all logins while keeping the rest intact was indeed correct?
SQL Server is a great tool, but not for every problem. In this case, just load the script in a text editor and use the find & replace function to chagne all occurences of domain1 to domain2
February 16, 2016 at 1:31 pm
Hugo Kornelis (2/16/2016)
So the assumption made previously that you need to change the domain name for all logins while keeping the rest intact was indeed correct?
Good point, actually the accounts names would be slightly different Domain1\John would become Domain2\IQ_John, there isn't a pattern for the destination accounts.
I do realize and have mentioned in my original post,t-sql is probably not the best solution for this. I was looking more for like a powershell or some sort of scripting
February 16, 2016 at 1:42 pm
Well, if the names change as well it become more cumbersome and a simple manual replace no longer cuts it.
Options I would look into would be a text editor with scripting, a simple C# or VB program, or as you already mention Powershell. We do have a Powershell forum in here as well, though that is mostly geared towards using powershell for SQL tasks, you might find help there. Or you could find a non-SQL related forum on the internet.
(Note: it probably is possible to do this in SQL Server as a mental exercise for fun, but it's not the right tool and you'd be spending a lot of time to tweak it. I'm not going to spend time on this)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply