SCCM SQL Error

[FN-GM]

Hello,

 

I am seeing the below error in the SMS_DISTRIBUTION_MANAGER log. I have googled this but I can't find anyone who actually fixed it.

 

Does anyone have any suggestions please?

 

Thanks

 

Microsoft SQL Server reported SQL message 229, severity 14: [42000][229][Microsoft][sql Server Native Client 11.0][sql Server]The EXECUTE permission was denied on the object 'fnIsSnapshotInitializing', database 'CM_001', schema 'dbo'.

Please refer to your Configuration Manager documentation, SQL Server documentation, or the Microsoft Knowledge Base for further troubleshooting information.

[FN-GM]

Does anyone happen to know what account SCCM uses to talk to SQL please?

 

Thanks

[Geoff]

Generally the computer account of the management point. However you can override this setting in Admin -> Site Config -> Servers & Site System Roles -> -> Management point. Right click on Properties and check out the settings on the Management Point Database Tab.

 

Edited April 25, 2017 by Geoff

[FN-GM]

Thanks, its using the computer account. In SQL what account under logins would that be please? There isn't one named after the server.

 

Thanks

[Geoff]

No, the SCCM site server computer account needs to be listed in the local administrators group on the SQL server.

 

SCCM 2012 R2 – Site server computer account administrative rights failed | Jack Stromberg

[FN-GM]

No, the SCCM site server computer account needs to be listed in the local administrators group on the SQL server.

 

SCCM 2012 R2 – Site server computer account administrative rights failed | Jack Stromberg

 

Already is

[mukz]

Hey FM-GM,

 

Can you just for testing purposes change account to another one on SQL that has access to the sccm database? For example sccm_sql_sa or anything which has full access to the DBs

[Geoff]

SCCM needs the 'sysadmin' role on the SQL server. If your local admin group doesn't have that you have a broken SQL server.

[FN-GM]

SCCM needs the 'sysadmin' role on the SQL server. If your local admin group doesn't have that you have a broken SQL server.

 

These are the logins we have, the blanked out names are our IT staff. What group / account would it go under please?

 

thanks

 

- - - Updated - - -

 

Hey FM-GM,

 

Can you just for testing purposes change account to another one on SQL that has access to the sccm database? For example sccm_sql_sa or anything which has full access to the DBs

 

If all else fails I will give it a go.

 

Cheers

[FN-GM]

Hey FM-GM,

 

Can you just for testing purposes change account to another one on SQL that has access to the sccm database? For example sccm_sql_sa or anything which has full access to the DBs

 

I have done this and the error is still happening.

 

The user account is a member of a group that has sysadmin rights. Can't see that making a difference though?

Edited April 25, 2017 by FN-GM

[mukz]

I have done this and the error is still happening.

 

The user account is a member of a group that has sysadmin rights. Can't see that making a difference though?

 

I managed to recreate the error here in a test environment, but i managed to fix it by changing the owner to the newly created sccm sql user.

 

Sorry i couldn't help further. Will ask around to some people i know if they know of a solution.

[FN-GM]

i managed to fix it by changing the owner to the newly created sccm sql user.

 

How would I do that please?

[mukz]

How would I do that please?

 

Please check this first:

SSMS > Security > Logins > the SQL login account > User Mapping > should be mapped to CM_001 with that same user and the schema should be dbo.

I have smsschm_users also ticked as a role membership.

 

I ran this on the other test to get it working but i would test it somewhere to make sure yourself first.

 

GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO (sqlaccount)