Migrating users to new server - 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

General SQL Server Forums

New to SQL Server Programming

Migrating users to new server

Author

Topic

Paul2309
Starting Member

2 Posts

Posted - 2007-08-31 : 10:15:06

Hope somebody can help a novice, so I can impress my boss.

I've got to move a database to a new server and I'm trying to use Gregory A Larsen's sp_help_revlogin script http://www.databasejournal.com/features/mssql/article.php/2228611.

It works perfectly for all users, but I want to limit it to one database by using his suggestion shown on that page:

SELECT sid, name, xstatus, password FROM master..sysxlogins
Your_db..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'

However, when I make the changes, so it looks like this:

SELECT sid, name, xstatus, password FROM master..sysxlogins a join
int_test..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'

I get this error when I try to run it:

Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'sid'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'password'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.

Can anyone see what I'm doing wrong.

The database that I want to limit the script/procedure to is called int_test on the same server, and I've checked that the table sysusers does exist, and that it has a column called name.

Any help would be greatly appreciated.

Paul

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-31 : 11:01:07

You need to use the labels on each column reference

ie:
SELECT a.sid, a.name, a.xstatus, a.password FROM master..sysxlogins a join
int_test..sysusers b on a.sid = b.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

Words are but constrained thoughts.

Paul2309
Starting Member

2 Posts

Posted - 2007-09-03 : 04:36:01

Cheers Rick, works a treat.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-03 : 21:09:46

quote:
Originally posted by Paul2309

Cheers Rick, works a treat.

Glad I could help!

------------------------
Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources