Hi all,
I read some message in FAQ about my problem but i doesn't work any
more...
I have 2 SQL-Servers that use sql-server and windows security
integrity.
On first Server, i add a linkServer to the other by using
sp_addlinkedserver and use a specified user ...
So i can use select command, execute store procedure, update , insert
and any command to read,update or write data .
I said Coool :-) I could put arrival data from one database to the
other database by using a trigger !!!
Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
When the trigger is firing, and only when i want to execute the insert
command, anything happen ... Also the @.@.error don't work...
Let me show what happen in the trigger :
CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
AFTER INSERT
AS
DECLARE ...
SET XACT_ABORT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET implicit_transactions off
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT ...
FROM OPENXML (@.idoc, '/EXECID',2)
WITH ( ... )
EXEC sp_xml_removedocument @.idoc
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
TOTO')
SET @.v_StrMess = 'The problem is HERE ...'
--raiserror (@.v_StrMess , 16 , 1)
COMMIT TRAN
if @.@.error >0
BEGIN
raiserror (@.v_StrMess , 16 , 1)
END
SET implicit_transactions on
END
I know that it's possible to update a linkedserver whith a trigger but
i don't know how !!!
Please, could you explain in good word how to do it or is there
another solution ?
Thx for answers ...
ByeWhat is the error you are getting?
AMB
"Mick" wrote:
> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye
>|||@.@.Error is only captured for the very LAST executed statement. In your case,
you would always be 0.
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
@.@.error value here
You should change it to this to capture the @.@.error and set your @.msg
declare @.err int
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
IF @.err=0
COMMIT
ELSE
BEGIN
ROLLBACK
raiserror (@.v_StrMess , 16 , 1)
END
-oj
"Mick" <mickymickmc@.yahoo.fr> wrote in message
news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...
> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye|||Thx for your answer ...
But the problem is not the @.@.error message or else !!!
The problem is that when the insert instruction start, nothing happen
...
I run a trace to understand what sql-server that run trigger do but
when the BEGIN DISTRIBUTED TRANSACTION start , there is the end of
transaction and nothing else.
For example, when i make an insert into srvdb1 by using sql-query
analyser, the trigger start and i wait until the connection is
broken...
i will test something and go back later ...
"oj" <nospam_ojngo@.home.com> wrote in message news:<eruW3o7EFHA.2176@.TK2MSFTNGP15.phx.gbl>.
.
> @.@.Error is only captured for the very LAST executed statement. In your cas
e,
> you would always be 0.
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
> @.@.error value here
> You should change it to this to capture the @.@.error and set your @.msg
> declare @.err int
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
> IF @.err=0
> COMMIT
> ELSE
> BEGIN
> ROLLBACK
> raiserror (@.v_StrMess , 16 , 1)
> END
>
> --
> -oj
>
> "Mick" <mickymickmc@.yahoo.fr> wrote in message
> news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...
Showing posts with label updates. Show all posts
Showing posts with label updates. Show all posts
Sunday, March 25, 2012
Creating a Trigger on Table Access
Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, update
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:
> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, update
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:
> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>
Wednesday, March 7, 2012
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.
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.
Subscribe to:
Posts (Atom)