Re-add a user to SQL2005 database

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
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?
Question by:Milkybar-kid
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 26

Expert Comment

ID: 26287499
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.
LVL 10

Accepted Solution

laneduncan earned 2000 total points
ID: 26287761
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.
LVL 10

Expert Comment

ID: 26287782
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question