Windows security

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pmcguire - 15 Jul 2005 23:18 GMT

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?

Signature

Pat

Reply to this Message

Uri Dimant - 17 Jul 2005 07:52 GMT

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'

> 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
[quoted text clipped - 10 lines]
> (and grant it db_owner privileges) on SQL1.mydbase without creating the login
> on SQL1. Can this be done?

Reply to this Message

Awais Bin Khalid - 24 Jul 2005 21:40 GMT

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

> Hi
> There are two stored procedures provided by Microsoft to transfer
[quoted text clipped - 26 lines]
> login
> > on SQL1. Can this be done?

Reply to this Message

Vern Rabe - 26 Jul 2005 19:11 GMT

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

> 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
[quoted text clipped - 48 lines]
> > login
> > > on SQL1. Can this be done?

Reply to this Message