Creating a DB User *not* on current Machine

During my development phase, I publish updates to my testing server
environment by literally detaching and copying my SQL Server DB files to tha
t
machine and re-attaching them. This works fine and is very convenient. The
only problem is that each time I do it, I have to create a user in my DB
after I have copied it to the destination server which matches the username
under which IIS is running web access. This is a user which does not exist o
n
the instance where I do my development.
I was hoping that there was a way to create a user in the database with a
windows login which deosn't exist on that machine, but it fails when I try t
o
do that, saying that that user doesn't exist. Is there *any* way to get a
Windows Authentication-style DB user to exist in my database where that user
doesn't exist in on the current server so that when I publish the database
files, I won't have to go in and create that user in the database each time?
Does this question make sense?
AlexOn Jun 11, 10:11 am, Alex Maghen <AlexMag...@.newsgroup.nospam> wrote:
> During my development phase, I publish updates to my testing server
> environment by literally detaching and copying my SQL Server DB files to t
hat
> machine and re-attaching them. This works fine and is very convenient. The
> only problem is that each time I do it, I have to create a user in my DB
> after I have copied it to the destination server which matches the usernam
e
> under which IIS is running web access. This is a user which does not exist
on
> the instance where I do my development.
> I was hoping that there was a way to create a user in the database with a
> windows login which deosn't exist on that machine, but it fails when I try
to
> do that, saying that that user doesn't exist. Is there *any* way to get a
> Windows Authentication-style DB user to exist in my database where that us
er
> doesn't exist in on the current server so that when I publish the database
> files, I won't have to go in and create that user in the database each tim
e?
> Does this question make sense?
> Alex
If windows login is a domain user , you can create that login in your
database server. If not , create a windows login in your database
server with the same name and (may be same password).Provide proper
acces to login .
Database user should also map to proper windows login .
After restoring the database run the sp on the restored database
exec sp_change_users_login 'auto_fix','database username'
this will link login with user|||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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||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 procedur
e
> 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 Serve
r
> 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
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/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/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello Alex,
As I mentioined, the database user is mapped to the SQL login by SID. Even
the user name on both machines are the same, the SIDs are the different.
Therefore, I don't think it could meet your requriement.
However, if you use a domin user such as domain1\user, you could add the
user as login on both server, and map to the database user at one server
for a database could be mapped to properly if the database is moved to
another database.
You could find the SIDs of logins and database users from the following
queries:
select * from master..syslogins
select * from sysusers
As Srinivas mentioned, sp_change_users_login could help in the situation if
you don't want to map the user/login yourself.
Please keep in mind that sp_change_users_login 'auto_fix' corrects an
orphan user by either:
1. If a matching login does not exist -> create it with a blank password.
2. If a matching login exists -> update SID
If you have further questions or comments on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.