Logshipping issue

We are using SQL2005 Logshipping between our primary and secondary SQL
server. It works great but we do have one problem.
The systemdatabases can't be configured for logshipping by design. If I
create a new user or change something on an exsisting user this is not been
updated on my secondary server, because this is saved in the master database.
I can't just create the same user on the secondary server because all
userdatabase is readonly so I can't grant a user access on userdatabases.
How do you handle this issue? Do I have to reconfigure Logshipping everytime
I make changed in my master database?There is no problem if you just add a new user in a user database for an
existing login. But you have to manage any change you may make to the master
database. This includes adding a new login or any change to a login SID that
may screw up the mapping between the login and its users.
Take a look at these two KB articles and the discussions of sp_resolve_logins:
http://support.microsoft.com/kb/321247/en-us
http://support.microsoft.com/kb/314515/en-us
Linchi
"Thomas" wrote:
> We are using SQL2005 Logshipping between our primary and secondary SQL
> server. It works great but we do have one problem.
> The systemdatabases can't be configured for logshipping by design. If I
> create a new user or change something on an exsisting user this is not been
> updated on my secondary server, because this is saved in the master database.
> I can't just create the same user on the secondary server because all
> userdatabase is readonly so I can't grant a user access on userdatabases.
> How do you handle this issue? Do I have to reconfigure Logshipping everytime
> I make changed in my master database?

Logins, Users, Roles, Schemas

Hi everyone,
I have just migrating my database From SQL2K to SQL2005. Users, Roles
and Schemas were all migrated. No issues there. Logins, on the other
hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
orphans, correct?
If I re-create the login and assigning the login to the database, it
will give an error saying that the user already exists. So, I delete the
User, then it prompts me that I have a schema associated with it, so I
delete the schema, then create the user. Roles have not been touched.
Must I also update the roles? and I'm not sure what schemas are in
comparison to SQL 2000.
Please advise of a better way to deal with users, logins, roles and
schemas.
Thanks,
Audrey
*** Sent via Developersdex http://www.codecomments.com ***Perhaps these resources will help you.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have just migrating my database From SQL2K to SQL2005. Users, Roles
> and Schemas were all migrated. No issues there. Logins, on the other
> hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
> orphans, correct?
> If I re-create the login and assigning the login to the database, it
> will give an error saying that the user already exists. So, I delete the
> User, then it prompts me that I have a schema associated with it, so I
> delete the schema, then create the user. Roles have not been touched.
> Must I also update the roles? and I'm not sure what schemas are in
> comparison to SQL 2000.
> Please advise of a better way to deal with users, logins, roles and
> schemas.
> Thanks,
> Audrey
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for these articles. I have a slightly different issue. We moved a SQL
2000 database from server "A" to server "B" and there are now orphaned
users. They are actually old admins, etc., that we don't even want any more.
How do you delete these from a database? Is it as simple as just deleting
the logins from the database?
I haven't found anything on this topic and I would like some assurance that
it should work without croaking the database.
Thanks,
Ray
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Perhaps these resources will help you.
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
>|||Yes, if they are truly 'orphans', you can just delete them.
However, if they 'own' any objects, the deletion will fail. You will then
have to find those objects, and change the ownership to [dbo] before try
ing
the deletion again.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Do sp_change_users_login 'report' to get a list of orphans before you decide
to do anything.
Also, check out this kb for more info.
http://support.microsoft.com/kb/274188
-oj
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Thanks to the both of you.
Ray
"oj" <nospam_ojngo@.home.com> wrote in message
news:eSR$4wgzGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Do sp_change_users_login 'report' to get a list of orphans before you
> decide to do anything.
> Also, check out this kb for more info.
> http://support.microsoft.com/kb/274188
>
> --
> -oj
>
> "Ray" <no@.repliesplease.com> wrote in message
> news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
>

logins

Hi,
How do I transfer all logins from sql2k server to a new sql2005 box?
ThanksYou can use many approaches:
- Copy Database Wizard
- A SQL 2000 DTS package
- A SQL 2005 SSIS package
Also you can use the following script
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Solid Quality Learning
http://www.sqlu.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"mecn" <mecn2002@.yahoo.com> escribi en el mensaje
news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do I transfer all logins from sql2k server to a new sql2005 box?
> Thanks
>|||Thanks, All I need to do is transfer to master.logins table
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||thanks
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

login problems after database move

I'm trying to move a darabase from one sql2005 server to another sql2005 server within the same domain.

I have tried both backup/restore and attach/dettach and get the same problem.

With this database I'm using a SQL Server user, the user name is on both servers with exactly the same password.

On the new sql 2005 server after I have moved the database to it, if I goto security->Users and do properties on the username. It says the user is set to "Without login" how do I change this to "Login name" ?

thanks,

martin

Hi there,

I believe, from your description, that you haven't resolved the user logins after restoring your database backup to the new server.

You can read more about this in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;240872

You can fix your problem by following the instructions in the article but I'm getting the impression from your post you only want to fix a single user (which is a SQL Server account & not a Windows domain account) so it might be quicker to use the "sp_change_users_login" stored procedure.

You can find more info on the stored proc in Books Online, but here's a link to it on the web:
http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I believe that the syntax you have to use is:

EXEC sp_change_users_login @.Action = 'Update-One', @.userNamePattern = '<Database User Name>', @.login = '<SQL Server Login>'

Hope that helps a bit but sorry if it doesn't
|||

Thanks problem solved.

martin

Login problem

Hi, I'm having a problem connecting to my SQL2005 database, and I'm
wondering if it's even possible to do what I'm trying to do. I have a
domain user set up as the dbo of a database (domain\myuser). Through
the SQL Server Management Studio, I want to connect to the database as
that user, but I am logged into my computer as me (domain\me). So
when I open up the SQL Server Management Studio, when I select use
Windows Authentication, it fills in my username, and I cannot change
it because it's greyed out. Is it possible for me to somehow
authenticate to the server as domain\myuser instead?
Thanks
Dylan
Since you use Windows Authentication, meaning, whoever logged in the
computer, SQL Server sees it as authenticated. So, when you log into your
computer with an account that does not have access to the SQL
Server/database, then you cannot get in. If you have given the account
Domain\MyUser access to the SQL Server/database, then you need to log into
the computer as Domain\MyUser. That is how Windows Authentication works.
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1181224874.713354.288520@.g4g2000hsf.googlegro ups.com...
> Hi, I'm having a problem connecting to my SQL2005 database, and I'm
> wondering if it's even possible to do what I'm trying to do. I have a
> domain user set up as the dbo of a database (domain\myuser). Through
> the SQL Server Management Studio, I want to connect to the database as
> that user, but I am logged into my computer as me (domain\me). So
> when I open up the SQL Server Management Studio, when I select use
> Windows Authentication, it fills in my username, and I cannot change
> it because it's greyed out. Is it possible for me to somehow
> authenticate to the server as domain\myuser instead?
> Thanks
> Dylan
>
|||Or, to phrase it differently:
The functionality is found in Windows and called "Run As". :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Norman Yuan" <NotReal@.NotReal.not> wrote in message news:eLKjuQRqHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Since you use Windows Authentication, meaning, whoever logged in the
> computer, SQL Server sees it as authenticated. So, when you log into your
> computer with an account that does not have access to the SQL
> Server/database, then you cannot get in. If you have given the account
> Domain\MyUser access to the SQL Server/database, then you need to log into
> the computer as Domain\MyUser. That is how Windows Authentication works.
> "dylan" <dylan.roehrig@.gmail.com> wrote in message
> news:1181224874.713354.288520@.g4g2000hsf.googlegro ups.com...
>
|||On Jun 7, 12:24 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Or, to phrase it differently:
> The functionality is found in Windows and called "Run As". :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Norman Yuan" <NotR...@.NotReal.not> wrote in messagenews:eLKjuQRqHHA.1296@.TK2MSFTNGP06.phx.gbl. ..
>
Thanks, yeah, I was basically wondering if that was the only way to do
it. thanks.

Login problem

Hi, I'm having a problem connecting to my SQL2005 database, and I'm
wondering if it's even possible to do what I'm trying to do. I have a
domain user set up as the dbo of a database (domain\myuser). Through
the SQL Server Management Studio, I want to connect to the database as
that user, but I am logged into my computer as me (domain\me). So
when I open up the SQL Server Management Studio, when I select use
Windows Authentication, it fills in my username, and I cannot change
it because it's greyed out. Is it possible for me to somehow
authenticate to the server as domain\myuser instead?
Thanks
DylanSince you use Windows Authentication, meaning, whoever logged in the
computer, SQL Server sees it as authenticated. So, when you log into your
computer with an account that does not have access to the SQL
Server/database, then you cannot get in. If you have given the account
Domain\MyUser access to the SQL Server/database, then you need to log into
the computer as Domain\MyUser. That is how Windows Authentication works.
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1181224874.713354.288520@.g4g2000hsf.googlegroups.com...
> Hi, I'm having a problem connecting to my SQL2005 database, and I'm
> wondering if it's even possible to do what I'm trying to do. I have a
> domain user set up as the dbo of a database (domain\myuser). Through
> the SQL Server Management Studio, I want to connect to the database as
> that user, but I am logged into my computer as me (domain\me). So
> when I open up the SQL Server Management Studio, when I select use
> Windows Authentication, it fills in my username, and I cannot change
> it because it's greyed out. Is it possible for me to somehow
> authenticate to the server as domain\myuser instead?
> Thanks
> Dylan
>|||Or, to phrase it differently:
The functionality is found in Windows and called "Run As". :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Norman Yuan" <NotReal@.NotReal.not> wrote in message news:eLKjuQRqHHA.1296@.TK2MSFTNGP06.phx.
gbl...
> Since you use Windows Authentication, meaning, whoever logged in the
> computer, SQL Server sees it as authenticated. So, when you log into your
> computer with an account that does not have access to the SQL
> Server/database, then you cannot get in. If you have given the account
> Domain\MyUser access to the SQL Server/database, then you need to log into
> the computer as Domain\MyUser. That is how Windows Authentication works.
> "dylan" <dylan.roehrig@.gmail.com> wrote in message
> news:1181224874.713354.288520@.g4g2000hsf.googlegroups.com...
>|||On Jun 7, 12:24 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Or, to phrase it differently:
> The functionality is found in Windows and called "Run As". :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Norman Yuan" <NotR...@.NotReal.not> wrote in messagenews:eLKjuQRqHHA.1296@.
TK2MSFTNGP06.phx.gbl...
>
>
Thanks, yeah, I was basically wondering if that was the only way to do
it. thanks.

Login problem

Hi, I'm having a problem connecting to my SQL2005 database, and I'm
wondering if it's even possible to do what I'm trying to do. I have a
domain user set up as the dbo of a database (domain\myuser). Through
the SQL Server Management Studio, I want to connect to the database as
that user, but I am logged into my computer as me (domain\me). So
when I open up the SQL Server Management Studio, when I select use
Windows Authentication, it fills in my username, and I cannot change
it because it's greyed out. Is it possible for me to somehow
authenticate to the server as domain\myuser instead?
Thanks
DylanSince you use Windows Authentication, meaning, whoever logged in the
computer, SQL Server sees it as authenticated. So, when you log into your
computer with an account that does not have access to the SQL
Server/database, then you cannot get in. If you have given the account
Domain\MyUser access to the SQL Server/database, then you need to log into
the computer as Domain\MyUser. That is how Windows Authentication works.
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1181224874.713354.288520@.g4g2000hsf.googlegroups.com...
> Hi, I'm having a problem connecting to my SQL2005 database, and I'm
> wondering if it's even possible to do what I'm trying to do. I have a
> domain user set up as the dbo of a database (domain\myuser). Through
> the SQL Server Management Studio, I want to connect to the database as
> that user, but I am logged into my computer as me (domain\me). So
> when I open up the SQL Server Management Studio, when I select use
> Windows Authentication, it fills in my username, and I cannot change
> it because it's greyed out. Is it possible for me to somehow
> authenticate to the server as domain\myuser instead?
> Thanks
> Dylan
>|||Or, to phrase it differently:
The functionality is found in Windows and called "Run As". :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Norman Yuan" <NotReal@.NotReal.not> wrote in message news:eLKjuQRqHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Since you use Windows Authentication, meaning, whoever logged in the
> computer, SQL Server sees it as authenticated. So, when you log into your
> computer with an account that does not have access to the SQL
> Server/database, then you cannot get in. If you have given the account
> Domain\MyUser access to the SQL Server/database, then you need to log into
> the computer as Domain\MyUser. That is how Windows Authentication works.
> "dylan" <dylan.roehrig@.gmail.com> wrote in message
> news:1181224874.713354.288520@.g4g2000hsf.googlegroups.com...
>> Hi, I'm having a problem connecting to my SQL2005 database, and I'm
>> wondering if it's even possible to do what I'm trying to do. I have a
>> domain user set up as the dbo of a database (domain\myuser). Through
>> the SQL Server Management Studio, I want to connect to the database as
>> that user, but I am logged into my computer as me (domain\me). So
>> when I open up the SQL Server Management Studio, when I select use
>> Windows Authentication, it fills in my username, and I cannot change
>> it because it's greyed out. Is it possible for me to somehow
>> authenticate to the server as domain\myuser instead?
>> Thanks
>> Dylan
>|||On Jun 7, 12:24 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Or, to phrase it differently:
> The functionality is found in Windows and called "Run As". :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Norman Yuan" <NotR...@.NotReal.not> wrote in messagenews:eLKjuQRqHHA.1296@.TK2MSFTNGP06.phx.gbl...
> > Since you use Windows Authentication, meaning, whoever logged in the
> > computer, SQL Server sees it as authenticated. So, when you log into your
> > computer with an account that does not have access to the SQL
> > Server/database, then you cannot get in. If you have given the account
> > Domain\MyUser access to the SQL Server/database, then you need to log into
> > the computer as Domain\MyUser. That is how Windows Authentication works.
> > "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >news:1181224874.713354.288520@.g4g2000hsf.googlegroups.com...
> >> Hi, I'm having a problem connecting to my SQL2005 database, and I'm
> >> wondering if it's even possible to do what I'm trying to do. I have a
> >> domain user set up as the dbo of a database (domain\myuser). Through
> >> the SQL Server Management Studio, I want to connect to the database as
> >> that user, but I am logged into my computer as me (domain\me). So
> >> when I open up the SQL Server Management Studio, when I select use
> >> Windows Authentication, it fills in my username, and I cannot change
> >> it because it's greyed out. Is it possible for me to somehow
> >> authenticate to the server as domain\myuser instead?
> >> Thanks
> >> Dylan
Thanks, yeah, I was basically wondering if that was the only way to do
it. thanks.