Permissions error when trying to sync with db at web hosts

Ben Mills

I'm trying to sync a remote copy of a SQL Server 2000 database at my web hosting company with a local SQL Server 2005 copy. When I try, I get the following error message:

SELECT permission denied on object 'syslogins', database 'master', owner 'dbo'.

Does SQL Compare really need to access the master database? I'm surprised that any web hosting company allows this, although I know it works with another I use. Is there any way around this? If not, can you give me a list of the permissions I need so that I can ask for these permissions?

The weird thing is that I really don't want to sync the logins. I know they are different and that is fine.

I'm having the same issue with SQL Data Compare.

Thanks,
Ben

david connell

Hi Ben,
Yes under SQL 2000 we SELECT information from dbo.syslogins in order to get details about the logins. (A different mechanism is used under SQL2005)

The SQL Data Compare engine uses some of the base functionality of SQL Compare engine, this is why SQL Data Compare has the same issue.
I hope this helps.

David Connell

Ben Mills

So if I move to hosting with a SQL Server 2005, then I'll only need permissions on my own database?

I'm sure there are technical reasons that you need to access the master database, but I can see why some web hosting companies would not want to give me any permissions to the master database. Hopefully moving to 2005 will help.

Thanks,
Ben

david connell

Hi Ben,
Yes SQL Compare will not directly access the master database when running under SQL 2005.

David

david connell

Sorry meant to say the SQL Compare Engine.. The SQL Compare GUI will access the master database when the user requested a list of available databases for a given SQL instance.

Ben Mills

Thanks for the help. I guess I'll move to a different hosting company that gives me SQL Server 2005.

david connell

Hi Ben,
It has been pointed out to me that although we don’t make explicit links into master for SQL2005, many of the databases system views do. As a result there are many repercussions when denying a user access to master. You can verify this by using SQL Dependency Tracker. So moving over to SQL2005 will not help.

I setup a SQL 2000 Blank database, which had a user called Fred attached to it. Fred was denied access to master.
I then granted the following permissions,

GRANT SELECT ON dbo.syslogins TO Fred

Everything then worked OK. This is only a starting position. I hope this helps.
Best regards
David

Ben Mills

I'm not sure I fully understand the explanation of why this won't work with SQL Server 2005. Did you try the same test with a SQL Server 2005 database? I would be very interested to see the results as I have the option to switch to SQL Server 2005 at my web hosting company.

Thanks,
Ben

Ben Mills

I spoke to the engineer at my web hosting company (aspwebhosting.com). They made it very clear that giving any user access to the syslogins table is a very bad thing that allows someone to see all the usernames. A hacker then only needs to guess the passwords for each login (he described it as giving someone half the combination to the safe). That really makes a lot of sense to me and I understand why they are not willing to give me that permission.

I really think that there should be an option that eliminates the calls to the syslogins table if I'm not trying to sync the logins. All I want to do is sync the tables and stored procedures.

Thanks,
Ben

Jeff Randall

It's not ideal, but you could always import the DB (structures only, not data) into a local copy of SQL and do the compare locally...

Ben Mills

Thanks Jeff, that could work for me. Like you say, it's not ideal, but at least I could generate a SQL script to make the schema changes.

Unfortunately, it doesn't work for SQL Data Compare, which I like to use to get a local copy of the data to work with. SQL Data Compare also seems to need access to the master database.

I'm really lost without these tools. I've got so used to these tools being part of my development process that it's REALLY hard to lose them.

Ben

Jeff Randall

For anything that you need SQL DataCompare, you'd have to import the table(s) with the data, which will take longer, but would allow you to continue to use SQL Data Compare...

Ben Mills

Thanks Jeff. That's a great idea.