RE: Creating a DB User *not* on current Machine

Just one bit of confusion I have about all of this: Creating a user in the
database which is based on a Windows User is no problem: I can create the
same username on both my Dev and Staging machines. But I *think* the problem
is that the username that you need to provide in SQL Server has the machine
name built into it:

<Machine Name>\<User Name>

So even if I have exactly the same user name on both machines, will I be
able to do what I want? By the way, this is SQL Server 2005.

Thanks again.

Alex



""Peter YangMSFT]"" wrote:

Hello Alex,

From your description, I understand that you'd like to create a DB user to
exist in your database matched to a not existing Windows login before you
detach/attach the database to a new server.

I'm afraid this option is not available due to the behavior of SQL
Server.When you move a database from one server that is running SQL Server
to another server that is running SQL Server, a mismatch may occur between
the security identification numbers (SIDs) of the logins in the master
database and the users in the user database.

By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide
the sp_change_users_login system stored procedure to map these mismatched
users. However, you can only use the sp_change_users_login stored procedure
to map standard SQL Server logins and you must perform these mapping for
one user at a time.

In SQL Server 7.0 or later versions, you can maintain the mapping between
the logins in the master database and the users in the user database by
using the SIDs. This mapping is required to maintain correct permissions
for the logins in the user databases. When a login on the new server does
not have matched SID of one of the user on the database moved, you have to
create a new database base user to map them. Also, it's not possible to
create a local user on a different machine.


The best option is that you use a domain user so that the login exist on
the both SQL Servers. After you move the database, the SID is matched
automatically and you don't need to re-create the DB user for the login.

You may want to see the following article for more details:


314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546

240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133

If anything is unclear or you have further comments, please feel free to
post back. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


.

Relevant Pages

  • RE: database access from VC++
    ... But my database already exist in SQL server 2005. ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.vc.database)
  • Re: Problem with SQL Server 2005 Express Edition
    ... Use of included script samples are subject to the terms specified at ... One cause for that error is trying to use a SQL Server login on a database server that is configured to only accept Windows logins. ...
    (microsoft.public.sqlserver.security)
  • Re: transfering ASPNETDB.MDF Authentiocation to SQLServer
    ... ** Since SQL Server Express's database(your current mdf data file based ... database) are just a standard SQL server 2005 data file, ... Microsoft MSDN Online Support Lead ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: VB.Net app send information via HTTP to SQL server
    ... since database server(such as SQL Server is ... Microsoft MSDN Online Support Lead ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.languages.vb)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)