| Author |
Topic |
|
poser
Posting Yak Master
124 Posts |
 Posted - 2011-11-01 : 15:09:08
|
| I restored a database from another server and run the exec sp_change_users_login 'report' and my user does not show up as orphaned.But the user name is displayed differently:Server login is Test1 and the database login is test1?How do I fix this whithout messing up the password?Thanks for your helpr/p |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2011-11-01 : 15:17:27
|
| This is the error message I get:Msg 916, Level 14, State 1, Line 1The server principal "test1" is not able to access the database "PGIP"under the current security context.Thanks, r/p |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-01 : 15:25:05
|
| Here's how I would fix it if you are having a case sensitive issue:1. Script out the login using this query, making sure to display the results in text and not grid mode:SET NOCOUNT ONSELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''',', @defdb = ''' + dbname + '''',', @deflanguage = ''' + language + '''',', @encryptopt = ''skip_encryption''',', @passwd =', cast(password AS varbinary(256)),', @sid =', sidFROM sysloginsWHERE name NOT IN ('sa')AND isntname = 02. Grab just Test1 into a new window. This will include your password.3. Delete Test1 from logins in SSMS. Do NOT touch test1 in the user database.4. Now go to your other window with Test1 and modify the account to test1.5. Run it.6. See if you can login now.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2011-11-01 : 15:28:14
|
| Master and database are both SQL_Latin1_General_CP1_CI_ASr/p |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-01 : 15:32:19
|
| The process I posted should work regardless of case sensitivity. Test1 should be equivalent to test1 on a server not respecting case sensitivity, and sp_change_users_login seems to handle it when I did a brief look at the code. So I am not sure why you aren't seeing it come up as an orphaned user when it does appear that's the issue.If the process I posted doesn't work, then it wasn't an orphaned login issue.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2011-11-01 : 15:51:08
|
| Okay I think I see what is happening.The query is trying to pull from another db on the server.When I run the exec sp_change_users_login 'report' usertest1 shows up as an orphan.Can I correct this in this db only and not mess up the password?Thank you for your patience.r/p |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2011-11-01 : 16:12:54
|
| I still have a question ;)Server Login: Test1Database Login: test1which one would go first?Would this be the exact script:EXEC sp_change_users_login 'Update_One', 'Test1', 'test1'Thank you so much for helping...r/p |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
sp_change_users_login