Monday, September 30, 2013

How to resolve issue with SQL configuration manager?

Got the Below error message while trying to invoke Configuration manager.

Error Message: Cannot connect to WMI Provider. You do not have permission or the server is unreachable. Note that you can only manage SQL server 2005 and later servers with configuration Manager. Inavlid namespace [0x8004100e].

Resolution : Need to search for .mof files in c:\program files\microsoft sql server folder.
The solution is to go to a command prompt and then run mofcomp.

C:\Program Files\Microsoft SQL Server\90\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

The output will look like below Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180 Copyright (c) Microsoft Corp. 1997-2001. All rights reserved. Parsing MOF file: C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmprovider xpsp2up.mof MOF file has been successfully parsed Storing data in the repository... Done! If it throws error then we need to rebuild the WMI repository as below:

net stop winmgmt
c: cd %systemroot%\system32\wbem
rd /S /Q repository
regsvr32 /s %systemroot%\system32\scecli.dll
regsvr32 /s %systemroot%\system32\userenv.dll
mofcomp cimwin32.mof
mofcomp cimwin32.mfl
mofcomp rsop.mof
mofcomp rsop.mfl
for /f %%s in ('dir /b /s *.dll') do regsvr32 /s %%s
for /f %%s in ('dir /b *.mof') do mofcomp %%s
for /f %%s in ('dir /b *.mfl') do mofcomp %%s
echo DONE

Monday, September 16, 2013

How to Reset permission for Public role in SQL server?

Few days back user called saying that they are getting the below error in their application.

"The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'"

As a temporary fix,we have given sysadmin to carry out their business. Later after troubleshooting we have identified that some how permissions for public role on the server were modified/corrupted. Only sysadmin role could resolve their issue. Other than that no other server role solved thier problem of accessing.

Immediately a question came to my mind "How to reset permission for Public role ?"

To reset the permission for Public role/Guest user, execute the below code on a good server which will provide you the complete rights for PUBLIC role. Copy the output and paste to the server with the issue.

SELECT SDP.state_desc , SDP.permission_name , SSU.[name] AS "Schema" , SSO.[name] , SSO.[type] FROM sys.sysobjects SSO INNER JOIN sys.database_permissions SDP ON = SDP.major_id INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid ORDER BY SSU.[name] , SSO.[name] .

Note: Please do not perform this directly on production environment. Better test it in UAT prior to implementing it on Production server.