We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Showing posts with label instances. Show all posts
Showing posts with label instances. Show all posts
Tuesday, March 27, 2012
Database growth and Replication
We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegro ups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||That was it. Missed the autoshrink option.
Thanks
|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegrou ps.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegro ups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||That was it. Missed the autoshrink option.
Thanks
|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegrou ps.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database growth and Replication
We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Wednesday, March 7, 2012
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
Subscribe to:
Posts (Atom)