November 20, 2013 at 9:43 pm
I copied logins from 2005 to 2012 through Database copy wizard. All the logins copied over correctly. But none of them seem to work.
I didn't copy all the databases yet. Out of 20 dbs, I copied only 2 dbs over and then the logins. Not sure if this has to do anything.
November 21, 2013 at 2:31 am
This is windows logins or SQL? DBs that are default for them are copied to a new instance? What mean "But none of them seem to work." can't login, any error, app don't work??
November 21, 2013 at 5:21 am
Did you check this
select sp.name,sp.[type],sp.type_desc,sp.is_disabled, sp.default_database_name,sp.modify_date
from sys.server_principals sp
and then
ALTER LOGIN [login_name] ENABLE
GO
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
November 21, 2013 at 7:06 pm
Some of them were disabled. I enabled them and used the password to login; but it still says Login failed for user....
November 22, 2013 at 3:20 am
Try only for one user which login was disabled.
(1) drop the user and then (2) recreate the user
and see how it's going to be.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 22, 2013 at 6:06 am
You're also going to need to make sure the default database for the login exists. If the default database doesn't exist or the login doesn't have permission to it, you won't be able to login.
select name, sid, default_database_name
from sys.server_principals
where name = 'xxx';
Also, check to see if the SIDs match between the login and user. If they don't match, the user doesn't actually have access to the database. It looks like it does in SSMS, but the SIDs must match.
select name, sid
from sys.database_principals
where name = 'xxx';
November 22, 2013 at 7:43 am
What is the error message that you are getting? I haven't used the copy wizard in ages, but I'm fairly certain that it doesn not bring the passwords over for security reasons. Instead, it puts some sort of hash in the password and you will need to chagne that to the correct password.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 22, 2013 at 8:04 am
It returns the default database from sys.server_principals but it doesn't return that SQL logins from sys.database_principals. But if you go through SSMS, that user has valid permission to the DB that I am trying to connect.
November 22, 2013 at 8:14 am
In your previous post you put in the begining of the error message - Login failed for - but didn't put in the rest. Please put the entire message in.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 22, 2013 at 8:17 am
Do the SIDs match? Meaning the SID from sys.server_principals and the SID from sys.database_principals have to be equal.
November 22, 2013 at 8:21 am
it didn't return any record for that user from sys.database_principals. But if you go through ssms, this user has correct permisson to the db.
November 22, 2013 at 8:28 am
SQL_Surfer (11/22/2013)
it didn't return any record for that user from sys.database_principals. But if you go through ssms, this user has correct permisson to the db.
First make sure you've set your database context correctly and you're not querying a different database's sys.database_principals table. If that's the case, I would drop the user and recreate it. See if the problem magically goes away.
November 22, 2013 at 8:35 am
SQL_Surfer (11/22/2013)
it didn't return any record for that user from sys.database_principals. But if you go through ssms, this user has correct permisson to the db.
If this query does not return some rows (in the database context) and you see the users via ssms then you have to recreate the users.
select dp.name, sp.name,dp.principal_id,dp.type,dp.type_desc,sp.is_disabled from sys.database_principals dp
join sys.server_principals sp on sp.sid = dp.sid
where dp.type='U'
P.S. Good point from Ed Wagner
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 22, 2013 at 8:36 am
Sorry I was using the wrong DB context. Yes SID exactly matches.
November 22, 2013 at 8:40 am
SQL_Surfer (11/22/2013)
Sorry I was using the wrong DB context. Yes SID exactly matches.
Did you try, for a disabled login which you enabled, to drop the user and the recreate it and see if it works?
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply