• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Re-add a user to SQL2005 database

If a user is removed from a database in error can it be re-added just by using the same name?

i.e User fred has dbo_owner rights and owns table fred.table1

fred is deleted as a user then re-added as dbo_owner

Will "new" fred have the same permissions as "old" fred?
  • 2
1 Solution
if you are adding the user with the db_owner privileges then the user by default has the privilages to access all the database objects, define DDL and DML operations... db_owner is a strong role from the point of privilages.
The DB user is separate from the SQL Login username; if you just removed the login from the instance, then the database user still exists; you just have to re-create the login and run
EXEC sp_change_users_login UPDATE_ONE , sqlusername, sqlloginname
within that database context.

If the database user was deleted, the login still should exist, and you'll do the same thing, only after re-creating the db user.  Note that any schemas this db user had would have been dropped when the user was deleted.  That's probably not a problem, since it sounds like it operated in the dbo schema.
Ick.  I see that the quotes got munged above.  The sp_change_users_login should be:
exec sp_change_users_login 'update_one', 'sqlusername', 'sqlloginname'

where  sqlusername is the db user, and sqlloginname is the instance login.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now