SQL SERVER 2005 with Windows 7 Problems

Question

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.

Answer 1

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.