Take the 2-minute tour ×
Server Fault is a question and answer site for professional 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:

The weird thing is that if I access the production database from my machine I can see the properties option. So, there is really something wrong with Windows 7 security.

UPDATE 2:

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
    
I know this has nothing to do with the problem but why are you running it on W7 - it's not supported –  Chopper3 Jan 22 '10 at 22:22
    
SQL SERVERE 2005 is not supported on Windows 7 Ultimate!!!! Are you kidding me? –  user21004 Jan 22 '10 at 22:24
    
I have SQL Server 2005 express and dev versions running just fine on Windows 7. Not sure where @Chopper3 is getting his info. –  GregD Jan 22 '10 at 22:41
    
There might be some problem with the restore process. I cannot do the right click and select properties on sprocs. –  user21004 Jan 22 '10 at 22:57
    
fair enough - I'm wrong, you've taught me something. –  Chopper3 Jan 22 '10 at 23:04
show 1 more comment

1 Answer

You probably have orphaned users. When you are accesing the server from your machine your domain credentials probably have access as DBadmin to the production server. Run this code to detect orphaned users:

Use TestDB
sp_change_users_login 'report'

The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the TestDB database, and the sysxlogins system table in the master database. to fix the problem:

Resolve Orphaned Users

Use TestDB
sp_change_users_login 'update_one', 'test', 'test' 

SELECT sid FROM dbo.sysusers WHERE name = 'test'
0x40FF09E48FBD3354B7833706FD2C61E4

use master
SELECT sid FROM dbo.sysxlogins WHERE name = 'test'
0x40FF09E48FBD3354B7833706FD2C61E4

This relinks the server login "test" with the the TestDB database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.

share|improve this answer
    
Thanks! I can see two users when I run the first command. then I run the second command like this Use mydatabase sp_change_users_login 'update_one', 'azamsharp', 'azamsharp' and get the following result: Terminating this procedure. The Login name 'azamsharp' is absent or invalid. –  user21004 Jan 23 '10 at 4:48
    
If I delete the two users from the database they come back again. –  user21004 Jan 23 '10 at 4:55
    
To sync the user to a login you first have to create the login at the instance level. –  mrdenny Jan 23 '10 at 6:53
    
The login with "azamsharp" is already created for the database. I can see it in the security => users. I have deleted dbo1 and not I need to map "azamsharp" to the correct login name. It says " The Login name 'azamsharp' is absent or invalid." –  user21004 Jan 23 '10 at 7:15
    
Somehow I managed to solve the orphan user problem but I still cannot run the stored procedure prefixed with "azamsharp". Like "azamsharp.getarticles". It says no stored procedure found. –  user21004 Jan 23 '10 at 7:24
show 1 more comment

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.