asked
Help fixing / editing "public" server role permissions in SQL Server Express
I am having difficulty getting some NETWORK Services to access some of the databases in my SQL Server 2005 Express on an SBS 2008 box with SharePoint and WSUS, etc. Typically Event ID: 3760 I've been tracking this down, item by item with a working server and have found a discrepency with the troubled server and a working server with the "Server Roles" for "public". The working machine has correct permissions in the Server Roles for "public". (TSQL, etc) The problem machine is giving me the error when I try to view the permissions in SQL Server Express: Failed to retrieve data for this request (Microsoft.SqlServer.Express.SmoEnum) Additional information |- An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo) |- Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CPI_CI_AS" in the UNION operation. (Microsoft SQL Server: 468) After dismissing the error I am able to Add the roles that are missing, but them don't Apply, and the error comes up the next time I try to verify the setting. A screen shot from a healthy server is:
ftp://sbs.jefric.com:22/Public_ServerRoles-Good.jpg A screen shot from the problem server is:
ftp://sbs.jefric.com:22/Public_ServerRoles-NoGood.jpg Is there a way around this error to add / correct the "Server Roles" for "public" in SQL Server 2005 Express? Thank you, Alan
Are these servers supposed to be setup idential for the SQL install? Can you check the colation of the working server verses the non working server? To change the colation is not difficult, I would be curious if they are different. If they are and are supposed to be the same we can walk you through that, if they are supposed to be different, I can give you the syntax to resolve the coaltion issue.
SQL\_Latin1\_General\_CP1\_CI\_AS collation can be translated as - Sort rules: Latin1_General - Codepage: CP1 meaning 1252 - Case sensitivity: CI meaning **c**ase **i**nsensitive - Accent sensitivity: AS meaning **a**ccent **s**ensitive The KS\_WS piece probably hints that the database in question is SharePoint: KS_WS stands for **K**ana-**s**ensitive, **w**idth-**s**ensitive. It looks like the only way to resolve the issue is to get the guts of the statement which is invoked when you try to view the permissions via GUI and modify the statement to include collate SQL\_Latin1\_General\_CP1\_CI\_AS when selecting data from the SharePoint database. Same will apply to the script used to correct server roles for public. Edit --> Added the links to articles explaining the problem with collations and how to solve it far better than I can: - [Cannot resolve the collation conflict][1] - [Collations in SQL Server, examples and restrictions][2] Oleg [1]:
http://justgeeks.blogspot.com/2008/10/error-cannot-resolve-collation-conflict.html [2]:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx
thanks for the information Oleg. I agree, but the error is being generated when selecting the "Permissions" option in the SQL Server 2005 Expres GUI (see JPG link above). I am not sure what SQL I have confirmed that the colation conflict exists, but this problem does not exist on other SBS servers that have the same colation conflict.
@jefricllc Exactly. Selecting **the "Permissions" option in the SQL Server 2005 Expres GUI** does nothing more than executes the SQL statement under the hood. This statement has a problem because it uses union which has a problem with collation (as it should have). If you can write the script to view permissions in the query editor and the script to alter these permissions then you will not have a problem with collation. The problem with GUI will still remain, but who cares.
So both your servers are setup the same, same collation on the instance, same collation on the db's? Just one gives you the error and the other doesn't? Are they the same version of SQL? You original question was is there away around getting the server role fixed. If all you are needing to do is modify the permissions you can do it from command line using sqlcmd. I am with @trad and @oleg, what steps were taken to get you to this point and have you tried executing the statement directly?
thanks for the reply. the two servers are indeed identical, sql, windows, etc just one seems to have a corrupt server role for public. if you can look at the JPG, I am unable to set the securables and explicit permissions on the problem server: A screen shot from a healthy server is:
ftp://sbs.jefric.com:22/Public_ServerRoles-Good.jpg A screen shot from the problem server is:
ftp://sbs.jefric.com:22/Public_ServerRoles-NoGood.jpg i am unfamiliar with what the exact SQL commands to use to examine and set these values. any help you can offer would be greatly appreciated. Thanks
