SQL Users / Database Moves - 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 2005 Forums

SQL Server Administration (2005)

SQL Users / Database Moves

Author

Topic

Krandall
Starting Member

6 Posts

Posted - 2007-09-26 : 16:52:10

Alrighty.... I'm a long time listener and a first time caller here.
I've been reading multiple topics dealing with my issue but none seem to really address what I'm doing.

We have 3 separate enviroments, Dev, QA, and Prod. Quite frequently we have a database that gets moved from our Dev Server to QA, or QA to Prod, or Prod to QA ect...

What we have been doing is when a database is moved, it holds all of the actual database logins, but when you look within the actual server logins there's nothing there (dealing w/ that specific database). So we then have to go first through all of our logins on the database write them down, then go one by one and create them on the server.

I'm wondering if there is a more simple way to be doing this to cut down on our administration time?

3/4 of our ID's on the database are all linked through our Domain using windows authentication. And since we keep all of our "application/local SQL ID's" enviromentally separate. (each ID XXX has its own ID for Dev, QA and Prod... XXXdev, XXXqa, and XXXprod) so we'll have to do those manually anyways, but I'm really hoping someone has a solution to this timely administration process!

Thanks for all of your help!
-Randy

Information Security Analyst
Securian Financial Group
St.Paul Mn,

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 16:56:24

Here's what I use:
http://www.sqlmag.com/articles/index.cfm?articleid=16090&

It even copies passwords and sids over so unorphaning them is not necessary.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Krandall
Starting Member

6 Posts

Posted - 2007-09-26 : 17:25:23

That is VERY cool!! Pretty much exactly what I was looking for, all I will need to do is push the results to an excel spreadsheet, do some data modifying (to only pull the specific DB I'm looking for) and run it on the new server!!!

Thank you SO much for your help!

-Randy

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

Krandall
Starting Member

6 Posts

Posted - 2007-09-26 : 17:27:49

Actually, one more question:

Here's one part that I run:

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1

Is there a way to tell this to only pull from ZZZZzzzz Database?
-Randy

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 17:44:11

You'd have to join to sysusers in that database.

FROM syslogins l
INNER JOIN ZZZZzzzz..sysusers u
ON l.CantRememberColumnName = u.CantRememberColumnName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Krandall
Starting Member

6 Posts

Posted - 2007-10-03 : 17:45:12

does anyone have the column names that it should be pulling from? I am having trouble finding which one it should be?
Thanks for all the help,
Randy

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-03 : 17:49:18

SID

Krandall
Starting Member

6 Posts

Posted - 2007-10-24 : 17:46:18

Okay, late response :)

Thanks for that one column.
I looked and found the columns that I'd like to pull (Name, SID, Roles, and Password) (I'm still kind of a script newbie) I am getting an error:

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins l
INNER JOIN dbdm0100..sysusers.u
ON u.name = name

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogins'.
Server: Msg 7202, Level 11, State 1, Line 1
Could not find server 'dbdm0100' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

any ideas?

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-24 : 17:53:41

It appears you are running this from the "master" database. You also have "dbdm0100..sysusers.u". That's saying the server is dbdm0100, database is blank (the ..), owner is sysusers, and table is u. Make it "dbdm0100..sysusers u" (take out the last .). (u is an alias)

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-24 : 17:58:00

Also, I'd be willing to bet that this code is here on this site somewhere (maybe in the scripts forum). I've written this stuff before and still have it on CD somewhere (have no clue where since it was a couple jobs ago). I'd look on here or the web before you spend much time writing, tweaking, and perfecting your script. You could have your answer (code) in 2-3 min maybe and it do everything (stuff you aren't thinking of right now) for you.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-24 : 18:00:12

If you chose to keep on writing your own script, don't forget to look at server roles too and make sure you add those from syslogins.

Krandall
Starting Member

6 Posts

Posted - 2007-11-15 : 17:24:50

quote:
Originally posted by Van

Also, I'd be willing to bet that this code is here on this site somewhere (maybe in the scripts forum). I've written this stuff before and still have it on CD somewhere (have no clue where since it was a couple jobs ago). I'd look on here or the web before you spend much time writing, tweaking, and perfecting your script. You could have your answer (code) in 2-3 min maybe and it do everything (stuff you aren't thinking of right now) for you.

Yeah, I've searched, but there's nothing on there that I can find that specifically points just to a database. Everything I've found relates directly to a server move vs. Individual Database moves (from 2000-05').

I'm having issues w/ the inner join.

I got the query to work just pulling the info from the server logins.

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM master..syslogins l

which returns all the users from syslogins. But for whatever reason when I run this:
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM master..syslogins l
INNER JOIN dgis0100..sysusers u
ON u.name = u.name

It gives my results duplicate lines. each login shows up 45 lines worth...

but not giving me the info just from the database I'm looking for... I feel sql beating me up.

"When you do things right, people won't be sure you've done anything at all."
Bender Bending Rodríguez - Futurama

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-15 : 17:31:22

You have this:

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM master..syslogins l
INNER JOIN dgis0100..sysusers u
ON u.name = u.name

Change the last line to:
ON u.name = l.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources