March 15, 2009 at 6:12 am
So, I see how sp_help_revlogin creates a script so I can create the login on the new server, but it's not getting permissions to the databases. For example, I have a SQL login called ClientApp. On the 2000 server it has "dbo" permission on Database1, and Read & Write on Database2.
When I generate the script to create the login, there's nothing assigning it permissions on DB1 and DB2. The database restored into 2005 from 2000 has a login called ClientApp brought over from the old server, but looking at the GUI and database mapping for the SQL Login, none of the databases are checked. Is there a way to get the database permissions to ClientApp ?
March 15, 2009 at 6:45 am
The permissions that users have in the DB are stored in the user database and not on the server level. There is no need to script those permissions if you restore the database to a different server, because they exist in the restored database. You might have a problem that the user in the restored database is mapped to a none existing login, but this is handled when you transfer the logins between the servers with the original SID.
By the way, if you are transferring logins from SQL Server 2000 to SQL Server 2005, you should use sp_help_revlogin_2000_to_2005 instead of sp_rev_login. You can get it from this URL - http://support.microsoft.com/kb/246133
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 15, 2009 at 8:16 am
I thought I had run sp_help_revlogin_2000_to_2005 , but still didn't see the databases assigned to the sql account. I dropped the SQL login and re-ran sp_help_revlogin_2000_to_2005 and I think it's ok, so must have been some mistake on my part. Thanks
March 15, 2009 at 3:56 pm
homebrew01 (3/15/2009)
I thought I had run sp_help_revlogin_2000_to_2005 , but still didn't see the databases assigned to the sql account. I dropped the SQL login and re-ran sp_help_revlogin_2000_to_2005 and I think it's ok, so must have been some mistake on my part. Thanks
After you move logins and restore the db.
in the restored db for each user run:
sp_change_users_login 'auto_fix','username' this will sync users to logins.
March 15, 2009 at 5:12 pm
all the sp_revlogin sp will do is create sql accounts on the new server with same 'sids' as the old server, when the db gets restored from the old server to the new server, all users in the db will map to the new logins on the new server by way of the matching sids. and thus all perms should still be in tact for each login and user...at least thats the thinking. So in theory you won't need to run the Sp_Change_Users_Logins sp to fix the sids.
Gethyn Elliswww.gethynellis.com
March 16, 2009 at 5:38 pm
In addition to the other posts, I would also make sure any logins you bring over from sql 2000, ensure that the password is either a strong password, or turn off password policy and password expiration on the login.
by default, SQL Server will turn them on when they are created from the sp_help_revlogin script from SQL 2000. This is a good practice, but sometimes in larger environments, it is hard to enforce password policies without causing application downtime.
Best wishes,
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply