RE: Stored Procedure Question regarding users

From: John E Davis (JohnEDavis_at_discussions.microsoft.com)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 14:23:01 -0800

thanks for the additional pointer.

This forum is awesome.

"AUttendorf" wrote:

>
> You can run the procedure as is substituting Pubs with your database name.
> If this does not create the login for you (which it should), you can create
> the login manually and use the update_one option. The syntax would then read:
> USE <yourdbnamehere>
> GO
> EXECUTE sp_change_users_login 'update_one', loginname, loginname
>
> "John E Davis" wrote:
>
> > I have found the following instruction set for fixing an existing user that
> > was restored to a new SQL server. I understand that the database being
> > restored maintains the users that were associated to it when the database was
> > backed.
> >
> > Now that I am restoring the database to a new SQL server instance (with the
> > same server name) I should be able to run the below command to create the SQL
> > logins for the users who already exist in the database that I am restoring,
> > correct?
> >
> > Anyways, I am fairly new to running SQL Stored Procedures and would like to
> > know information regarding this command.
> >
> > First: I see the entry for "USE pubs" preceeding the "EXEC" I am assuming I
> > would put the name of the database that I just restored here since my users
> > are located in that database...correct?
> >
> > Second: The new SQL server does not have the users created for SQL logins on
> > the system therefore, according to this command I should be able to create
> > the users during this Stored Procedure...correct?
> >
> > Let me know if I am off in the manner in which to run this.
> >
> > Thanks,
> >
> > C. Automatically map a user to a login, creating a new login if necessary
> > This example shows how to use the Auto_Fix option to map an existing user to
> > a login with the same name, or create the SQL Server login Mary with the
> > password B3r12-36 if the login Mary does not exist.
> >
> > USE pubs
> > go
> > EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
> > go
> >
> >

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • XPe SQL Database Backup/Restore
    ... This is a general FYI to anyone having difficulty with restoring the XP ... Embedded Component Database. ... Microsoft SQL Server Management Studio Express ...
    (microsoft.public.windowsxp.embedded)
  • Stored Procedure Question regarding users
    ... I understand that the database being ... Now that I am restoring the database to a new SQL server instance (with the ... Automatically map a user to a login, creating a new login if necessary ...
    (microsoft.public.sqlserver.server)