Re: Windows security
From: Vern Rabe (Rabe_at_discussions.microsoft.com)
Date: 07/26/05
- Next message: Vern Rabe: "Agent job owner/visibility"
- Previous message: BAG: "SQL2K:How to insure that data is NOT recoverable by forensic metho"
- In reply to: Awais Bin Khalid: "Re: Windows security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 26 Jul 2005 11:11:07 -0700
According to BOL, sp_change_users_password only works with SQL Server
Authentication, not Windows Authentication. I ran into the same problem and
came up with this script, which I run on the destination server immediately
after the restore:
exec sp_configure 'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
go
update sysusers
set sid = l.sid
from dbo.sysusers u
join master.dbo.syslogins l
on 'Win2\' + u.name = l.name
exec sp_configure 'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go
HTH
Vern Rabe
"Awais Bin Khalid" wrote:
> Every database contains a table called sysusers, which stores a list of all
> users who have access to that database. You can use following stored
> procedures to get detailed info and put same users on different databases.
>
> sp_change_users_login : Use this stored procedure to see and fix orphaned
> users.
>
> sp_validatelogins : This stored procedure reports information about orphaned
> Microsoft Windows NT/2000 users and groups that no longer exist in the
> Windows NT environment but still have entries in the Microsoft SQL Server
> system tables.
>
> Detail on following links:
> http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
>
> Hope this helps.
>
> Awais Bin Khalid
> Microsoft Certifiec Application Developer
> www.xavor.com
>
>
>
> "Uri Dimant" wrote:
>
> > Hi
> > There are two stored procedures provided by Microsoft to transfer
> > users/logins with their original SID.
> > Please do searching on the internet
> >
> >
> > If you login has an appropriate (a login should be created on remote
> > server)permissions perform a backup to another computer
> > BACKUP DATABASE DataBaseTO DISK =
> > N'\\Server\backup\Database.BAK'
> >
> >
> > "pmcguire" <pmcguire@discussions.microsoft.com> wrote in message
> > news:165C4A5A-8D38-4850-8C7A-7805B98D8A46@microsoft.com...
> > > I have 2 SQL servers (SQL1 and SQL2) residing on two Windows servers (Win1
> > > and Win2). I want to back up a database (mydbase) from SQL1 and restore
> > it
> > > to SQL2. I am using Windows authentication, and I would LIKE to be able
> > to
> > > do this without rebuilding the User list every time.
> > >
> > > For example, I want mydbowner to have db_owner rights on mydbase on both
> > > SQL1 and SQL2; Win1/mydbowner and Win2/mydbowner logins exist already,
> > and
> > > Win1/mydbowner already has db_owner privileges to SQL1.mydbase. When I
> > > backup and restore, Win1/mydbowner exists as a user on SQL2.mydbase, but
> > of
> > > course the login doesn't exist on SQL2.
> > >
> > > I guess what I would like to be able to do is create the user
> > Win2/mydbowner
> > > (and grant it db_owner privileges) on SQL1.mydbase without creating the
> > login
> > > on SQL1. Can this be done?
> > > --
> > > Pat
> >
> >
> >
- Next message: Vern Rabe: "Agent job owner/visibility"
- Previous message: BAG: "SQL2K:How to insure that data is NOT recoverable by forensic metho"
- In reply to: Awais Bin Khalid: "Re: Windows security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
