I am moving several databases that belongs to a single application from MSSQL2000 to MSSQL2005. After sucessfully moved and converted the databases I need to fix the users login and I am using sp_change_users_login procedure to do so. My questions are: I have an application user that needs to be fixed in several databases - how can I fixed the login all once Also is there a way to turned the check_policy off at the same time I fixed the logins
thank you
You will have to do that per database.
Check policy can ve turned of using:
I can't fix per database because once I run the procedure to fix the login for the first time it will not fix the same login for another database. As I said I have this same login in each database. This particular login is the application user. Any other ideas
If I understand the problem, you have the same user (i.e. the SID is the same) on all databases, correct You can find the SID using the following query:
SELECT sid FROM sys.database_principals WHERE name = 'user_name'
And then create a login with the corresponding SID and the password policy setting you need, for example:
CREATE LOGIN [login_name]
WITH PASSWORD = 'password',
CHECK_POLICY = OFF,
SID = 0x<SID_IN_BINARY_FORMAT>
Because the users are mapped to logins via the SID, all database users that are mapped to the same login will be fixed.
Please let us know if this information helped or if you have any additional questions.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Raul,
I do have the same user (application user) in several databases. But when I check the SID in each database they do not match (they all have different IDs). How to proceed thanks a lot.
It's hard to say without knowing what the purpose of these users is. It also looks like you've had this situation before the upgrade. If you just want to make all users map to the same login, you can create the login, like Raul indicated, and then you can map the users that have a different SID to this same login by using the ALTER USER ... WITH LOGIN statement that I discussed at http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx. This should make all users have that login's sid. But you should understand how you got in this situation in the first place - were these users supposed to really map to the same login From some background on how logins map to users, it may also help to take a look at this: http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx.
Thanks
Laurentiu
Raul,
I followed your instructions and I was able to create the login. Like I said this user is in several database and when I checked the properties after the login creation I noticed that the new login automatically mapped to most of the database its belong with exception of few. Those few database that didn't map to I scripted the "user as create to" and I noticed that it says "CREATE USER [username] WITHOUT LOGIN - and the others that got mapped to says "FOR LOGIN" instead without.
I went to the original MSSQL2000 databases that I obtained the backup in order to restore/move this dbs to MSSQL2005 and I didn't notice the same discrepancy. Do you have any idea why this is happening
thanks a lot
The ones that are created WITH LOGIN are the regular SQL users, and the ones marked WITHOUT LOGIN were created explicitly without login (see BOL: http://msdn2.microsoft.com/en-us/library/ms173463.aspx and I have some additional information in my blog: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx).
The part that is not clear to me is how could the users without login be created in SQL Server 2000. My only guess is that it may have been a self-correcting mechanism by the scripting tool in case the login was not found.
Try selecting the SID for those users, if the SID looks like a random bag of bytes, it is most likely a regular orphan user and you can use ALTER USER бн WITH LOGIN to re-map them to the correct login.
-Raul Garcia
SDE/T
SQL Server Engine