Attach a read-only restored database

hi all
can i attach a read only restored database. I restored a
database Test in Stand by Mode. After that i detached that database and
while restoring i m getting msg "Cannot attach a database that was
being restored.". Is it o that i can not attach a readonly restored
detached database ?
Parveen BeniwalHi
A read only database will remain read only if you restore it with the
RESTORE WITH RECOVERY option, you do not need to put it into standby as you
will not be restoring any transaction logs.
John
"Parv" wrote:

> hi all
> can i attach a read only restored database. I restored a
> database Test in Stand by Mode. After that i detached that database and
> while restoring i m getting msg "Cannot attach a database that was
> being restored.". Is it o that i can not attach a readonly restored
> detached database ?
> Parveen Beniwal
>|||Hi,
I think I have the same question, worded differently:
We are using our SAN to make snapshot copies of the database files of a
log-shipped copy of a database (of course these copies are in read-only
mode). We want to then use those copies. However, we cannot get those
files to attach. We get the same error "Cannot attach a database that was
being restored".
Is there some trick to attaching database files that are in read-only mode?
Note that it is ok if the database copy loses it's read-only status during
the attach process, since we don't plan to restore additional logs to that
copy.
Thanks!
--Chip Matthes
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A read only database will remain read only if you restore it with the
> RESTORE WITH RECOVERY option, you do not need to put it into standby as yo
u
> will not be restoring any transaction logs.
> John
> "Parv" wrote:
>|||Hi
This works fine! With restore the database stays read only, with attach it
gets reset
CREATE DATABASE [MyReadOnlyDatabase]
ON
( NAME = 'MyReadOnlyDatabase_data',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyReadOnlyDatabase_log',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
EXEC sp_detach_db [MyReadOnlyDatabase]
GO
EXEC sp_attach_db 'MyReadOnlyDatabase',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
BACKUP DATABASE [MyReadOnlyDatabase]
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
DROP DATABASE [MyReadOnlyDatabase]
GO
RESTORE DATABASE [MyReadOnlyDatabase]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
HTH
John
"Chip Matthes" wrote:
[vbcol=seagreen]
> Hi,
> I think I have the same question, worded differently:
> We are using our SAN to make snapshot copies of the database files of a
> log-shipped copy of a database (of course these copies are in read-only
> mode). We want to then use those copies. However, we cannot get those
> files to attach. We get the same error "Cannot attach a database that was
> being restored".
> Is there some trick to attaching database files that are in read-only mode
?
> Note that it is ok if the database copy loses it's read-only status during
> the attach process, since we don't plan to restore additional logs to that
> copy.
> Thanks!
> --Chip Matthes
> "John Bell" wrote:
>

Attach a read-only restored database

hi all
can i attach a read only restored database. I restored a
database Test in Stand by Mode. After that i detached that database and
while restoring i m getting msg "Cannot attach a database that was
being restored.". Is it o that i can not attach a readonly restored
detached database ?
Parveen BeniwalHi
A read only database will remain read only if you restore it with the
RESTORE WITH RECOVERY option, you do not need to put it into standby as you
will not be restoring any transaction logs.
John
"Parv" wrote:
> hi all
> can i attach a read only restored database. I restored a
> database Test in Stand by Mode. After that i detached that database and
> while restoring i m getting msg "Cannot attach a database that was
> being restored.". Is it o that i can not attach a readonly restored
> detached database ?
> Parveen Beniwal
>|||Hi,
I think I have the same question, worded differently:
We are using our SAN to make snapshot copies of the database files of a
log-shipped copy of a database (of course these copies are in read-only
mode). We want to then use those copies. However, we cannot get those
files to attach. We get the same error "Cannot attach a database that was
being restored".
Is there some trick to attaching database files that are in read-only mode?
Note that it is ok if the database copy loses it's read-only status during
the attach process, since we don't plan to restore additional logs to that
copy.
Thanks!
--Chip Matthes
"John Bell" wrote:
> Hi
> A read only database will remain read only if you restore it with the
> RESTORE WITH RECOVERY option, you do not need to put it into standby as you
> will not be restoring any transaction logs.
> John
> "Parv" wrote:
> > hi all
> > can i attach a read only restored database. I restored a
> > database Test in Stand by Mode. After that i detached that database and
> > while restoring i m getting msg "Cannot attach a database that was
> > being restored.". Is it o that i can not attach a readonly restored
> > detached database ?
> >
> > Parveen Beniwal
> >
> >|||Hi
This works fine! With restore the database stays read only, with attach it
gets reset
CREATE DATABASE [MyReadOnlyDatabase]
ON
( NAME = 'MyReadOnlyDatabase_data',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyReadOnlyDatabase_log',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
EXEC sp_detach_db [MyReadOnlyDatabase]
GO
EXEC sp_attach_db 'MyReadOnlyDatabase',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
BACKUP DATABASE [MyReadOnlyDatabase]
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
DROP DATABASE [MyReadOnlyDatabase]
GO
RESTORE DATABASE [MyReadOnlyDatabase]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
HTH
John
"Chip Matthes" wrote:
> Hi,
> I think I have the same question, worded differently:
> We are using our SAN to make snapshot copies of the database files of a
> log-shipped copy of a database (of course these copies are in read-only
> mode). We want to then use those copies. However, we cannot get those
> files to attach. We get the same error "Cannot attach a database that was
> being restored".
> Is there some trick to attaching database files that are in read-only mode?
> Note that it is ok if the database copy loses it's read-only status during
> the attach process, since we don't plan to restore additional logs to that
> copy.
> Thanks!
> --Chip Matthes
> "John Bell" wrote:
> > Hi
> >
> > A read only database will remain read only if you restore it with the
> > RESTORE WITH RECOVERY option, you do not need to put it into standby as you
> > will not be restoring any transaction logs.
> >
> > John
> >
> > "Parv" wrote:
> >
> > > hi all
> > > can i attach a read only restored database. I restored a
> > > database Test in Stand by Mode. After that i detached that database and
> > > while restoring i m getting msg "Cannot attach a database that was
> > > being restored.". Is it o that i can not attach a readonly restored
> > > detached database ?
> > >
> > > Parveen Beniwal
> > >
> > >

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>
sql

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
Gary
This is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>
|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (s
earch KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl..
.
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them
.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that t
he
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user wou
ld
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>