June 20, 2008 at 9:21 am
Okay, this maybe a simple question. When a login has access to a SQL server and multiple database; when you delete the login from the Server, but do NOT delete the logins on the database, can the access/permissions be re-established when you recreate the login on the server...?
Example being, I have a domain account w/ access to all databases on the server. If I delete the login from the server and leave the the database logins. If I recreate my domain account login a few weeks later, will those permissions be reestablished to the individual databases? Will it just reference the SID of the Domain ID in comparision of a SID in SQL....?
June 20, 2008 at 9:33 am
yes you can.
Just do a : sp_msforeachdb @command1='USE [?] EXEC sp_change_users_login ''report'' '
to check for all orphaned logins on these boxes.
it is the SQL Login that gets Orphaned when you try the same deletion and addition, but the WINNT Login remains in place.. but Just to statisfy the Query.. you should check for orphaned users...
Maninder
www.dbanation.com
June 20, 2008 at 9:48 am
Mani is correct and you can sync them back up. Remember the USERS are in the database and this is completely separate from LOGINs, which are on the server. If you want to remove a login, really you should disable it (ALTER LOGIN DISABLE) instead of deleting it if there is a chance it will be needed.
If you use Windows auth, this should be handled by moving people in and out of groups, not removing them from SQL Server.
June 20, 2008 at 12:27 pm
Great. I guess my concern was. If a login was delete on the server level, and recreated later, if the access would be re-established automatically.
sounds like the answer would be 'no'.
June 20, 2008 at 12:51 pm
Actually, the answer is "maybe".
If it is a Windows login, the SID is pulled from AD. It will be the same regardless of when you add it to the server. Any database users that were mapped to this login will work once again.
If it is a SQL login, you can run sp_help_revlogin (available fro Microsoft) before dropping it to get the old SID and password (hashed). If you delete the login, you can run the results of of sp_help_revlogin for the login you removed and it will work properly.
If you drop the SQL login without knowing the SID, you have little choice except to remap the new login to the database user for every database.
Generally, I agree with Steve. Don't delete it unless you mean it. Just because you "can" do something doesn't mean you "should".
Kyle
June 20, 2008 at 12:59 pm
Absolutely agree. But, to transition everything to domain authentication (AD Groups specifically), the (unfun) cleanup process needs to be completed to the current SQL/direct IDs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply