March 1, 2010 at 7:41 am
Hi guys,
Apologies if this has been posted before, however my serarch did not come up with anything.
Simple issue really, we have a lady who was recently married and needs her SQL login changed from *DOMAIN_NAME*\gallan to *DOMAIN_NAME*\gshepherd
I have looked into the ALTER LOGIN command however, it does not seem to like me renaming the login if there is a backslash in the name.
I have also tried when i leave out the *DOMAIN_NAME*\ part with no luck.
Obviously i have missed something obvious here! Would someone be able to advise??
Cheers,
Sam
March 2, 2010 at 4:58 am
I think you should change it from AD.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 2, 2010 at 10:30 am
Hi,
Yep has been changed in AD, however login on SQL has not changed.
March 2, 2010 at 11:06 am
BOL says ALTER LOGIN can be used to rename a login, the example is for a SQL login though:
--Changing the name of a login
--The following example changes the name of login Mary5 to Joe2.
--Copy Code
ALTER LOGIN Mary5 WITH NAME = Joe2;
--Lowell's best guess:
ALTER LOGIN [DOMAIN_NAME\gallan] WITH NAME = [DOMAIN_NAME\gshepherd];
--syntax=good
Lowell
March 3, 2010 at 6:57 am
I'd think it would be safer to script out the permissions for the old account, then modify for the new account and run, and then after testing, delete the old account.
There can be several places in which this can be stored - DB access, Roles, table permissions, etc.
At least this way you would have the old information out there where it's easier to see and reference should you miss something.
And you may also have applications that store some of this information, which you may want to synch up too.
Greg E
March 3, 2010 at 7:10 am
Greg Edwards-268690 (3/3/2010)
I'd think it would be safer to script out the permissions for the old account, then modify for the new account and run, and then after testing, delete the old account.
Unfortunately, i had bad experience with this script out approach.
weeks ago, i scripted out a sql login from one instance and pushed it in new instance.
but when i tried to login with it in new instance ,it gave
"Login failed for user 'cxxxx'. (Microsoft SQL Server, Error: 18456) ":w00t:
i didn't understand why it gave this error , i think password will also got replicated with it
but to make it run , i needed to refresh the password.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 8:21 am
Bhuvnesh (3/3/2010)
Greg Edwards-268690 (3/3/2010)
I'd think it would be safer to script out the permissions for the old account, then modify for the new account and run, and then after testing, delete the old account.Unfortunately, i had bad experience with this script out approach.
weeks ago, i scripted out a sql login from one instance and pushed it in new instance.
but when i tried to login with it in new instance ,it gave
"Login failed for user 'cxxxx'. (Microsoft SQL Server, Error: 18456) ":w00t:
i didn't understand why it gave this error , i think password will also got replicated with it
but to make it run , i needed to refresh the password.
Windows accounts will have no passwords.
You may want to try this - where you can specify a password in the script.
Also search for orphan logins to understand a bit more of what you ran into.
--Scripting Server Login (password is null) and Default Database
/* For security reasons the login is created disabled and with a random password. */
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TmyUser')
BEGIN
CREATE LOGIN [myUser] WITH PASSWORD=N'²¨?wÄö?nüÓW®¸?´ç??t?£?ªÞIza', DEFAULT_DATABASE=[DownTime_Staging], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [myUser] DISABLE
END
Greg E
March 3, 2010 at 11:11 pm
Bhuvnesh (3/3/2010)[hrbut when i tried to login with it in new instance ,it gave
"Login failed for user 'cxxxx'. (Microsoft SQL Server, Error: 18456) "
Here i am talking about sql authenticated login.
Do we need to refresh the password whenever we migrate any login to another instance.?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 11:18 pm
Bhuvnesh (3/3/2010)
Bhuvnesh (3/3/2010)[hrbut when i tried to login with it in new instance ,it gave
"Login failed for user 'cxxxx'. (Microsoft SQL Server, Error: 18456) "
Here i am talking about sql authenticated login.
Do we need to refresh the password whenever we migrate any login to another instance.?
No, we need not to do anything with password of the login already migrated to another instance.
however, i want to understand one thing, what exactly do you mean by:
we need to refresh the password .?
how do you refresh a password?
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 12:00 am
Refresh means resetting the password with same characters.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2010 at 12:09 am
Bhuvnesh (3/4/2010)
Refresh means resetting the password with same characters.
No, you don't have to reset the password, the moment you script out the login, it captures the password along with login.
-- Login: user1
CREATE LOGIN [user1] WITH PASSWORD = 0x01007D12E183D07FEADA3B7BE6C15A4EA8E47A0C5C34E4D HASHED, SID = 0x16E9161D432AED4B8CA85A002FDF, DEFAULT_DATABASE = [DB_Test], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 12:13 am
can you do a test for me ?. just create a sql auth. login in one instance and migrate it to another one and then try to login with it
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2010 at 12:16 am
Bhuvnesh (3/4/2010)
can you do a test for me ?. just create a sql auth. login in one instance and migrate it to another one and then try to login with it
sure.
but just want to let you know i've done this many a times for our reporting server.
just give me 2 minutes i'll post the result.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 12:34 am
Hi Bhuvnesh,
that worked like anything.
which method are you using to script out the login?
theres one catch to this, scripting the login and recreating the login on another server will only give you access to the server and not to the DB.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 12:44 am
CREATE LOGIN [MyLogin] WITH PASSWORD=N'ól?¨?&²??Ç[WÆ?$R¬sjµDúöÚ+pÇ', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MyLogin] DISABLE
i just created a login in one server and pushed it in another
and when i try to login with it.i got "login failed....." moreover i also set it with "sysadmin" (blind shot)
i also made it ENABLED
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply