users on restored database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

users on restored database

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-07-25 : 09:43:27

hi,
I automated the restore of a database to another server, but the logins always don't match. I ended up using a dts package but it doesn't make the database user a db_owner, is there any way in t-sql to do this. I was thinking of dropping the user from the database and recreating it with dbo priveleges..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:11:58

You can script out the users and their permissions and compile the script after the restore. Make it a second step in the job.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-25 : 11:22:45

Or use sp_change_users_login to remap db users.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 11:36:06

It is best to create the logins on the target server before doing the restore. When you do the restore, it will match up the logins.

CODO ERGO SUM

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-07-25 : 13:24:40

quote:
Originally posted by dinakar

You can script out the users and their permissions and compile the script after the restore. Make it a second step in the job.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

How can I do that in sql?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 13:30:16

Right click on DB -> All tasks -> Generate SQL Script => Options Tab -> under "Security Scripting Options" check 2,3,4 items. continue the wizard.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-07-26 : 07:57:26

I'm using the following query to grant db access but I keep getting the error:
Error 21776:[SQL-DMO]The name 'mbcuser3' was not found in the Users collection. If the name is a qualified name, user [] to separate various parts of the name, and try again.

use master
go
if not exists (select * from master.dbo.syslogins where loginname = N'mbcuser3')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'PracticeManager', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'mbcuser3', 'mbcuser3', @logindb, @loginlang
	EXEC sp_grantdbaccess 'mbcuser3', 'mbcuser3'
END
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-26 : 16:44:19

I was able to run the script and connect using user "bmcuser3" to my db. The only change I made was to Use my TestDB instead of master.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources