Local Accounts break the compare

StarPilot

I do work on a laptop that is not part of a domain. When I am at work, I am on a desktop that is part of the client's domain. Now, there is a local account created for ASP.NET applications called ASPNET. I am currently trying to copy a complete database from my laptop to my desktop. On the laptop, this account is BUMKHOUSE\ASPNET. On the desktop it is MTS65197\ASPNET.

I am getting all sorts of permission errors when I try to run the script produced by SQLCompare. To give you an idea here is a snippet of the code run with no modifications:

if not exists (select * from master.dbo.syslogins where loginname = N'BUNKHOUSE\ASPNET')
exec sp_grantlogin N'BUNKHOUSE\ASPNET'
GO
sp_grantdbaccess N'BUNKHOUSE\ASPNET', N'BUNKHOUSE\ASPNET'
GO 
 .
 .
 .
PRINT N'Altering permissions on [dbo].[AddProfile]'
GO
GRANT EXECUTE ON [dbo].[AddProfile] TO [BUNKHOUSE\ASPNET]
GO

RESPONSE WINDOW

Server: Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 41
Windows NT user or group 'BUNKHOUSE\ASPNET' not found. Check the name again.
Server: Msg 15401, Level 11, State 1, Procedure sp_grantdbaccess, Line 100
Windows NT user or group 'BUNKHOUSE\ASPNET' not found. Check the name again.
 .
 .
 .
Altering permissions on [dbo].[AddProfile]
Server: Msg 4604, Level 16, State 1, Line 1
There is no such user or group 'BUNKHOUSE\ASPNET'.

Now, if I go through and change the "domain name" part of the user this is what happens:

if not exists (select * from master.dbo.syslogins where loginname = N'MTS65197\ASPNET')
exec sp_grantlogin N'MTS65197\ASPNET'
GO
sp_grantdbaccess N'MTS65197\ASPNET', N'MTS65197\ASPNET'
GO
 .
 .
 .
PRINT N'Altering permissions on [dbo].[AddProfile]'
GO
GRANT EXECUTE ON [dbo].[AddProfile] TO [MTS65197\ASPNET]
GO

RESPONSE WINDOW

Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 127
User or role 'MTS65197\ASPNET' already exists in the current database.
 .
 .
 .
Altering permissions on [dbo].[AddProfile]
Server: Msg 4604, Level 16, State 1, Line 1
There is no such user or group 'MTS65197\ASPNET'.

Any one have any ideas? Before I go blogging about all of this I want to make it work. This is a great product as is and one I will always use. I just need help ASAP please.

Brian Donahue

Hi Keith,

Thanks for writing us about this. SQL Compare considers Windows users to be different from SQL users in a database, which is what causes this to happen.

For instance, compare two databases on two different servers: SERVERA and SERVERB. The databses are production and development, so you want to synchronize them. Both databases have a user called ASPNET.

Of course, in server A, the security user name is SERVERA\ASPNET and in server B, it's SERVERB\ASPNET. Sql Compare will flag this as a difference, and attment to add the ASPNET user. The trouble is, you've already got a user called ASPNET in the database, which refers to SERVERB\ASPNET.

Currently, there is just the way that the software functions. You could, as a workaround, either ignore permissions and do not script the users, than manually grant permissions to ASPNET if needed, or you could save the script to a file, open it in Query Analyzer, and delete the sp_grantdbaccess parts from it.

I hope this helps.

StarPilot

Hi Brian,

This did not solve the problem. I ended up taking my source DB offline, copied th MDF/LDF to the target server, brout the source back online and attached the DB on the target server.

Even when I commented out the sp_grantdbaccess I still got errors when it tried to grant permissions. Too much editing makes it a PITA to use. I even tried turning off all User/Group matching and it still tried to grant permissions. Maybe I'm missing something...

StarPilot

This is fast becoming a bone of contention.

Scenario 1: described above.

Scenario 2: I develop on an XP box with IIS 5. I have 2 accounts in the Database. [ASPNET] for the web app and [KBARROWS] for myself to work in SQL. I do not login as [SA], I login using trusted. Now, go to the Dev/QA/Prod servers and they all run on Win2003 and IIS6. There is 1 account there. [NETWORK SERVICE]. I try to script *anything* over and it rollsback because it could not find the [ASPNET] and [KBARROWS] accounts. It is taking me over 30 minutes per script to find and chop out all the GRANTS that cause the script to fail. Even when I turn off account scynch it still tries to create the accounts and use them. :x

Neil Davidson

Keith,

I apologise for the time it's taken to sort this out. We're going to work on a patch to add an 'Ignore users' option so we can get this working. This will take a few days.

In the short term, there are a couple of options:

1) You could set the options to ignore all permissions
2) You could remove the ASPNET and KBARROWS users' permissions and set up an ASP and MYUSERS groups, add the users to those groups, and then set the permissions for those groups. You will still need to edit the script, but only 1 line.

The second option is probably the best - it will make it a lot easier in the future for you.

- Neil