How to attach existing SQL-users to new Database

BrianResen

As part of a go-live senario on SQL 2005, I'm trying to find a easy way to copy the userlogin from one database (systemstest DB) to another (Go-live DB).

I have a systemstest-database and a future Go-live database on the same SQL2005 server. As part of test 70 users have been created with acces to the testdatabase. These 70 userlogin's I now need to give the same access to the Go-live database.

It would have to be a nested SQL query listing the userID's from the testDB and the granting the same permissions to the Go-liveDB.
I just don't know how to put this into SQL-statments.

Are there any one here that can help me out :?:

stryk

Hi,

well, once I had a similar issue, and I solved it with this script:

declare @userid varchar(20), @tsql varchar(1000)
declare user_cur cursor for select [User ID] from Test.dbo."User"
open user_cur
fetch next from user_cur into @userid
while @@fetch_status = 0 begin
  set @tsql =
'
use [master]
go 
CREATE LOGIN [' + @userid + '] WITH PASSWORD = '''', CHECK_POLICY = OFF
go
use [Navision]
go
CREATE USER [' + @userid + '] FOR LOGIN [' + @userid + ']
go
'
print @tsql
  fetch next from user_cur into @userid
end
close user_cur
deallocate user_cur

This generates a script to create logins/users on basis of the NAV table "User"; you could do it similar with a list e.g. from "syslogins" etc..

But you could also transfer logins e.g. from one SQL instance to another using the feature provided here: http://support.microsoft.com/kb/246133

Hope this helps you.

Regards,
Jörg

stryk

P.S.: Please regard that the "Password" with the TSQL is left empty!

BrianResen

Well, I don't need to recreate the users on the SQL-server. I just need to attach a new database to the "User mapping", but for about 70 or so userID's. Therfore my need for a scripting solution... O:)

ara3n

In SQL Server management studio. In Security->Login tree. On object explorer detail windows elect all the users right click and seelct scrip login ->Create->New query editor.

Copy and paste it into the other box and run it.

You only need to do this for SQL db users. For windows. After you restore the backup. WHen you synchronize Nav the windows users will be added to SQL automatically.