Orphan Users Throwing Exceptions

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)

Orphan Users Throwing Exceptions

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-01-23 : 18:43:06

When I try to create the following T-SQL

exec sp_change_users_login
@LoginName = 'dbo1',
@Password = NULL,
@action = 'Auto_Fix'

It throws the error:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

I restored the database from production on QA. Production has the user dbo1 which is an orphan user. The QA does not have dbo1 user. I don't need dbo1 because it is not used on production. So, I removed the "dbo1" user from the QA server users.

The database has many stored procedures which are "azamsharp.usp_getarticles". "azamsharp" is the login name. I have NO idea how to execute these stored procedures. I cannot even right click on the stored procedure and select properties. There is NOT EVEN a properties option to select.

Mohammad Azam
www.azamsharp.net

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 05:55:36

I normally do the following to reinstate an "orphaned" user after restoring a database on a new server. (If the login already exists, on the server, your don't need the AddLogin step)


USE MyDatabase
GO

EXEC sp_addlogin 'NewUserID', 'password', 'MyDatabase'

EXEC MyDatabase.dbo.sp_change_users_login 'Update_One', 'NewUserID', 'NewUserID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources