Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

First of all I restored the database from other server and now all the stored procedures are named as [azamsharp].[usp_getlatestposts]. I think [azamsharp] is prefixed since it was the user on the original server.

Now, on my local machine this does not run. I don't want the [azamsharp] prefix with all the stored procedures.

Also, when I right click on the Sproc I cannot even see the properties option. I am running the SQL Server 2005 on Windows 7.

UPDATE:

When I ran the orphan users stored procedure it showed two users "azamsharp" and "dbo1". I fixed the "azamsharp" user but "dbo1" is not getting fixed. When I run the following script:

exec sp_change_users_login 'update_one', 'dbo1', 'dbo1'

I get the following error:

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131 Terminating this procedure. The Login name 'dbo1' is absent or invalid.

share|improve this question

migrated from stackoverflow.com Jan 24 '10 at 1:22

This question came from our site for professional and enthusiast programmers.

1 Answer 1

A restore done via backup/restore will never ever rename any object in the database. The result of a database restore is the same database that was backed up. If the procedures are named in the restored database [schema].[object] then I guarantee you they were named just the same on the old server too.

What happened most likely is that you weren't aware that on the old server the user you logged in with had an implicit schema associated with it and when you issues statements like select from table or execute usp_foo you were automatically binded to your default schema.

You must check with the administrator of the old server to kindly tell you the permissions and defaults you had on the system and then ask the adminsitrator of the new server to gratiously restore you the same permissions and defaults.

share|improve this answer
    
how can I find the permissions myself and restore of my local machine? Or is there any option while restoring that it will create users and passwords automatically. –  user21004 Jan 23 '10 at 7:28
    
Your database permissions, and implicitly the default schema are already restored. What you need to do is log in on the new server with exactly the same login you logged in on the old server. –  Remus Rusanu Jan 23 '10 at 7:33
    
The new server is my local and it does take any username or password. It is based on windows authentication. The production server where I host my database takes username and password. –  user21004 Jan 23 '10 at 16:17
    
    
Please see my updated post! –  user21004 Jan 23 '10 at 22:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.